Friday, May 16, 2008

SSIS Trick - Setting multiple variables at once

There are custom components out there that set a single variable for you.  You can also set variables using script tasks. 

One way I found to set multiple variables in a package at the same time is to execute a 'fake' sql command. 

There is a tiny performance hit, however this could also be considered a way to include 'profiling' of your app variables with SQL Profiler.

1. Drag a SQL Command Task into the package.
2. Set the command connection.  Set the command text to SELECT 'Myvalue' as MyVariableValue, 'myvalue2' as My2ndVariableValue

Notice no FROM statement.  This also works for other databases, using DUMMY tables.
3. Set the resultset to 'Single Resultset'
4. Set the Results tab to map the variables to the select statement results.

No comments: