Post reply

DTS and Output from Stored Procedures

  • January 20, 2005 at 11:50 am

    #61646

    I am trying to get a return output from a Stored Procedure through DTS using an ExecuteSQL task.  I need to put this variable in a stored procedure, but can't seem to do it.  I have tried using an output variable as well as a return flag.  All I need is a status of the procedure to output.  Would anyone know how to do this through DTS?

     

    Thanks,

    Chris

  • January 20, 2005 at 1:52 pm

    #538391

    Try this in your Exec SQL Task...

    DECLARE @rc int

    DECLARE @rptdt datetime

    EXEC @rc = dbo.usp_MySproc @rptdt

    SELECT @rc AS MyOutputParam

    You can now assign the result of this last select statement to the output parameter of your Exec SQL task.

    [font="Courier New"]ZenDada[/font]

  • January 20, 2005 at 1:58 pm

    #538392

    I have tried that, with no help.

    DECLARE @status int EXEC

    @status = sp_check_Import_Table_Stores 'Stores_Import' SELECT @status AS Status

     

  • January 20, 2005 at 2:59 pm

    #538407

    Oops yeah, the output parameter from an ExecSQL task needs to be the result of a select query.

    Right now you have two sprocs, one depends on success of the other?  You could just incorporate this logic into your precedence logic in the dts package instead, couldn't you?

    [font="Courier New"]ZenDada[/font]

  • January 21, 2005 at 8:40 am

    #538527

    We had a similar issue. We ended up creating a control table to track the results. A process then can update, insert or delete rows as needed in this control table based on the future actions needed. Then other procedures can determine what action they need to do based on the data in the control table.

    This is similiar concept to a system registry in a Windows operating system.

  • January 21, 2005 at 10:01 am

    #538545

    Control tables are a very nice way to control execution and flow in DTS.  They can also be used to pass information to external packages.  You can also use the Message Queue Task.

    Chris, can you check @@error for your purposes?

    [font="Courier New"]ZenDada[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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