Post reply

How to pass input and output parameters in Execute SQL Task?

  • March 12, 2008 at 1:24 am

    #120459

    Hi,

    I have a SP which requires 3 inputs and retuns 3 outputs I want to store that output values to variables. I have declared 6 variables (3 for i/p & 3 for o/p) I am not able to map correctly please any body help me.

    my current settings are

    Connection: OLE DB

    SP: EXEC ODS_VLD_Header ?, ?, ?, ? output, ? output, ? output

    Resultset: None

    mapping: 0,1,2-i/p 0,1,2-o/p

    Please find above snapshots for more details

  • March 12, 2008 at 1:39 am

    #788412

    In parameter name, I think you have to mention the parameters from SP.

    For e.g

    if your SP is something like this

    ODS_VLD_Header (@Parm1 Int,@parm2 Int.. etc)

    You would need to mention @Parm1,@Parm2 under parameter name in parameter mapping. Also, you can get rid of the ?,? in the sql statement (shown in general.bmp)

    --------------------------------------------------------------------------------------
    Save our world, its all we have! A must watch video Pale Blue Dot[/url]

  • March 13, 2008 at 11:27 am

    #789230

    I have the same problem, need to pass a input parameter to a stored procedure. I have the following in General setting:

    ConnectionType = OLE DB

    Connection = ServerName.DBName

    SQLSourceType = Direct input

    SQLStatement = Exec dbo.spName ?

    My parameter needs to get date from

    "select Max(date) from TableName"

    I got rid of the "?", but the Build Query says procedure expects parameter, which was not supplied.

    What did I miss? How to map the parameter? Thanks.

  • March 13, 2008 at 1:32 pm

    #789286

    Attached a sample with ADO.net and OLEDB samples. Hope this helps.

    You can check this link for more detailed information

    http://technet.microsoft.com/en-us/library/ms141003.aspx

    --------------------------------------------------------------------------------------
    Save our world, its all we have! A must watch video Pale Blue Dot[/url]

  • March 19, 2008 at 11:49 am

    #791607

    Thanks for the example.

    I coded the max(date) in script task and saved it as a variable, then passed this variable to sp in Execute SQL Task.

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

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