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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Returning Values from a Stored Procedure (SQL...

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

Rate Topic

Display Mode

Topic Options

Author

Message

dave-dj

Posted Tuesday, September 23, 2008 7:37 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

dave-dj

Posted Tuesday, September 23, 2008 8:15 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

dave-dj

Posted Wednesday, September 24, 2008 9:37 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

mishaluba

Posted Friday, January 29, 2010 12:44 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:08 PM
Points: 525, Visits: 624

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

dave-dj

Posted Sunday, January 31, 2010 3:28 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)

Post #856870

riyer33

Posted Thursday, January 03, 2013 6:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 4:08 AM
Points: 37, Visits: 91

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

kl25

Posted Thursday, January 31, 2013 5:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 262, Visits: 1,167

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 »

Permissions