Using Expression Variables in SSIS

Here's a way to use Expression Variables in SQL Server Integration Services to dynamically create a connection string:

1. Create a new variable "SS_DST_Server"
2. Create a new variable "SS_DST_Database"
3. Create a new variable "SS_DST_Conn"
4. Set 'Use as Expression' property on SS_DST_Conn to true.
5. Under the expression, type in the connection string and append " + @[User::SS_DST_SERVER] + ";" where the server goes, and " + @[User::SS_DST_Database] + ";" where the database goes. Note it should start with =" and end with "

for example:
"Provider=SQLNCLI;Server=" + @[User::SS_DST_SERVER] +";Database=" + @[User::SS_DST_DATABASE] + ";UID=sa;PWD=asdasd;"

Now, instead of having to configure the connection string in a config file, you can store it in the package, and pass in the database and server name as a parameter.

There are other useful things you can do with this procedure.

Create a variable called "DATE_FILTER" and an expression variable "SQL_LOAD" with the expression "Select * from MyTable where mydate = '" + @[User::DATE_FILTER] + "'"

Dynamic SQL in your SSIS package.

Note variables are case-sensitive, so upper case is good practice.

Here's some sample connection strings...

www.connectionstrings.com