User Variables in SSIS

Posted by Jeff Rix on August 16, 2011 under SSIS | Be the First to Comment

To create a User Variable you need to bring up your variable window. One way to access this is to go to View > Other Windows > Variables. 

To add a variable click add variable (blue arrow). The scope will be set to the name of the package if you are in control flow and the name of the data flow if you are in a dataflow.

To set the variable using a Execute SQL Task item set the result set to single row. Make sure your SQLStatement only returns one row.

The Result Set then needs a name mapped to the user variable name

To use the variable in a data flow use a derived column and set a derived column name equal to the user variable.