Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Returning Values from a Stored Procedure (SQL TASK, SSIS) Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 7:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
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)
Post #574324
Posted Tuesday, September 23, 2008 8:15 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
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)
Post #574379
Posted Wednesday, September 24, 2008 9:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
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)
Post #575327
Posted Friday, January 29, 2010 12:44 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:42 AM
Points: 535, Visits: 750
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!


Post #856355
Posted Sunday, January 31, 2010 3:28 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
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)
Post #856870
Posted Thursday, January 3, 2013 6:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 67, Visits: 129
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.

Post #1402337
Posted Thursday, January 31, 2013 5:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:32 AM
Points: 488, Visits: 1,859
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.
Post #1414400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse