June 24, 2010 at 11:43 am
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
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
You may also use the Environment variables to store your variables
Raunak J
June 25, 2010 at 5:13 am
You may also use the Environment variables to store your variables
Raunak J
June 25, 2010 at 5:49 am
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