SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning Values from a Stored Procedure (SQL TASK, SSIS)


Returning Values from a Stored Procedure (SQL TASK, SSIS)

Author
Message
dave-dj
dave-dj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 1149
Hi all,

Here the problem:

I have the following stored procedure:


ALTER procedure [admin].[up_SetLogicalDate]
@QueryDate datetime
,@LogicalDate datetime OUTPUT
,@LogicalDate_Start datetime OUTPUT
,@LogicalDate_end datetime OUTPUT

--with execute as caller
as
begin

DECLARE @filter nvarchar(255) --Set the configuration filter value
,@ConfigDate datetime

SET @filter = 'LogicalDate'

--A null @date implies an increment: @date = @date + 1
IF @QueryDate IS NULL
begin
--Fetch the existing date
select @ConfigDate = convert(datetime, ConfiguredValue)
from admin.Configuration
where upper(ConfigurationFilter) = 'LogicalDate'

--Increment it by 1 day
set @LogicalDate = DATEADD(DAY,1,@ConfigDate)
END

SET @LogicalDate_Start = @LogicalDate
SET @LogicalDate_End = @LogicalDate + 1

SELECT @LogicalDate as [LogicalDate]
,@LogicalDate_Start as [LogicalDate_Start]
,@LogicalDate_End as [LogicalDate_End]

--Write the new @date to the table
update admin.Configuration
set ConfiguredValue = convert(datetime, @LogicalDate, 102)
where upper(ConfigurationFilter) = 'LogicalDate'

update admin.Configuration
set ConfiguredValue = convert(datetime, @LogicalDate_Start, 102)
where upper(ConfigurationFilter) = 'LogicalDate_Start'

update admin.Configuration
set ConfiguredValue = convert(datetime, @LogicalDate_End, 102)
where upper(ConfigurationFilter) = 'LogicalDate_End'

end
--proc


In short it optionally recieves a QueryDate and then returns a LogicalDate, LogicalDate_Start, LogicalDate_End.

Within SSIS I have used an SQL Task using an OLEDB connection with the parameters mapped to ordinals as shown below:







However, when I run the procedure I get the following error:

[Execute SQL Task] Error: Executing the query "exec [admin].[up_SetLogicalDate] null, ? output, ? output, ? output" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I have tried the query string without the 'output' for each question mark, I have also tried using parameter names such as @LogicalDate (which I then mapped in the parameter mapping screen), but no success.

I can't see what I've done wrong! I would be grateful for any help thanks.

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 1149
To add further to my confusion - I just checked the table the the parameters are linked to and it would appear the sql task container, is doing what I intend it to do



using the OLEDB connection, with Direct Input and the following command

admin.up_SetLogicalDate null, ?, ?, ?

Also, the parameters were mapped as Ordinals, with null defined for the sp input @QueryDate

Parameter Mapping
User::LogicalDate Output Date 0 -1
User::LogicalDate Output Date 1 -1
User::LogicalDate Output Date 2 -1

.........but still I get the the error


[Execute SQL Task] Error: Executing the query "admin.up_SetLogicalDate null, ?, ?, ?" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



Any clues, anyone????????????????

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 1149
Nearly there !!


I now have the task completing successfuly, but it appears that the variables aren't updating.

To resolve the error msg,

I basically had to map the parameters to the DBTIMESTAMP data type, with the package variable data types set to DateTime.




also, you may find this link useful if you have a similiar issue:


http://msdn.microsoft.com/en-us/library/ms345165.aspx

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 1006
I just wanted to thank you for sharing the details of your problem and especially the solution. You just saved me from going insane as I have been struggling with this very issue for the last 2 hours!



dave-dj
dave-dj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 1149
no problem.

I'd like to think I return the favour, as plenty of other forum members have help me in the past adn even know. :-D

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
riyer33
riyer33
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 201
Hello ,

I was struggling with the Execute SQL Task for the past 3 hrs, I had problems mapping the output parameters, result set and finally after resolving those , I had to struggle with the date !!
Your post helped me instantly in solving that.
kl25
kl25
Say Hey Kid
Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)

Group: General Forum Members
Points: 700 Visits: 1875
I add to the accolades. Thank you so much for posting the results of your investigations and how the situation was resolved. The error message did not send me down the path of looking at datatypes. Very much appreciate your generosity in sharing your solution. This was a difficult error to troubleshoot.
rgagne99
rgagne99
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 157
6 years later this post is still helpful. Now that's staying power.
Thanks
...Ray
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search