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

Question

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

dave-dj

SSChampion

Points: 11297
More actions
September 23, 2008 at 7:37 am

#124804

Hi all,
Here the problem:
I have the following stored procedure:
[font="Courier New"]
ALTER procedure [admin].[up_SetLogicalDate]
@QueryDatedatetime
,@LogicalDatedatetime OUTPUT
,@LogicalDate_Startdatetime OUTPUT
,@LogicalDate_enddatetime OUTPUT
--with execute as caller
as
begin
DECLARE @filter nvarchar(255) --Set the configuration filter value
,@ConfigDatedatetime
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)
fromadmin.Configuration
whereupper(ConfigurationFilter) = 'LogicalDate'
--Increment it by 1 day
set@LogicalDate = DATEADD(DAY,1,@ConfigDate)
END
SET@LogicalDate_Start =@LogicalDate
SET@LogicalDate_End=@LogicalDate + 1
SELECT@LogicalDateas[LogicalDate]
,@LogicalDate_Startas[LogicalDate_Start]
,@LogicalDate_Endas[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[/font]
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:
[font="Courier New"]
[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. [/font]
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)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dave-dj SSChampion Points: 11297 More actions · Answer 1

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

[font="Courier New"]admin.up_SetLogicalDate null, ?, ?, ?[/font]

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

[font="Courier New"]

[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.

[/font]

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

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 2

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 SSCertifiable Points: 5407 More actions · Answer 3

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 SSChampion Points: 11297 More actions · Answer 4

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. 😀

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

riyer33 SSC Enthusiast Points: 135 More actions · Answer 5

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 Hall of Fame Points: 3752 More actions · Answer 6

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 SSC Veteran Points: 206 More actions · Answer 7

6 years later this post is still helpful. Now that's staying power.

Thanks

...Ray