Post reply

Can and How do you pass a viable to a stored procedure in an ssis package from one task to another?

  • June 24, 2010 at 11:43 am

    #387216

    We are trying to pass a variable to a stored procedure within an SSIS package. We are just getting errors. I am wondering if this can be done in ssis 2005 and if anyone has any examples about how to do this.

    thanks!

  • June 25, 2010 at 4:47 am

    #1184903

    Create package level variables and pass those values across all tasks in the same package.

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • June 25, 2010 at 5:12 am

    #1184914

    You may also use the Environment variables to store your variables

    Raunak J

  • June 25, 2010 at 5:13 am

    #1184915

    You may also use the Environment variables to store your variables

    Raunak J

  • June 25, 2010 at 5:49 am

    #1184926

    Are you running the stored procedure using an Execute SQL Task? If so, the way you pass values to the SQL procedure depends on your Connection Type. If it's OLE DB, you identify a parameter with a ? and set Parameter Mapping for the parameters counting from 0.

    For example, if you want to run MyProcedure for the customer identified in your variable User::CustomerId, create an Execute SQL Task, open it and select OLE DB, then as SQLStatement set

    [font="Courier New"]EXEC [MyDB].[dbo].[MyProcedure] ?; [/font]

    then go to Parameter Mapping and set Parameter Number 0 to your variable name:

    Variable Name User::CustomerId Direction Input Data Type NVARCHAR Parameter Name 0 Parameter Size 16

    Other connection types have different syntax, including named parameters. I found this page clear and useful on the differences, (it's about Output parameters but the syntax is similar):

    http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

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

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