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

  • 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)

  • 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)

  • 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)

  • 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!

  • 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)

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

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

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

    Thanks

    ...Ray

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

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