SSIS package variables not visable in Execute SQL Task
-
I am using SSIS 2005 and have variables defined at the package level. When I try to execute a query in an Execute SQL Task it doesn't recognize my variable. In fact the query won't even parse. Example:
variable name : V_ROW_CNT
Sql Query: Select * from tablename where column_name > V_ROW_CNT
I also tried Select * from tablename where column_name > @V_ROW_CNT
and still the same problem.
I have hard time believing it is so hard to work with variables in SSIS. Why are they not visable in the other tasks?
-
In order to use your package variables in the SQL task, you must set them up in the Parameter Mapping page when you are editing the task.
For example, I want to execute the following statement (AdventureWorks sample database):
UPDATE Person.Contact SET EmailPromotion = 2 WHERE ContactID = @ContactID
I have an Int32 package variable I named MyVar, and set the value to 10.
First, I edit the SQL Task and change the SQL query text to (note the "?"):
UPDATE Person.Contact SET EmailPromotion = 2 WHERE ContactID = ?
Then I go to the Parameter Mapping page, and add a parameter. For the variable name, I select my variable ("User::MyVar"), set the direction to Input, and the Type as LONG. It's a Direct Input SQL statement, so the param name doesn't matter. If there are multiple params, it's the order that determines which var goes to which "?" in the query.
When I run the package, the EmailPromotion column in the Person.Contact row where ContactID = 10 is updated to the value of 2.
In your case, the query would be:
Select * from tablename where column_name > ?
...and you would add a parameter mapping for User::V_ROW_CNT as an input parameter.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply