Expressions in SSIS

So they are rather convoluted in my opinion, but very powerful.

Expressions in SSIS. The key to making dynamic packages is to use variables and expressions. Go to the variables window and create a new string variable, say DATE. (Use upper case for variables - they're case sensitive and this will save you headaches.) Then create another variable, let's call it SQL_QUERY. Make sure they're both string. Set the default date to 20050112 and the default sql to select * from mytable where date = '20051112'.

Here's where it gets interesting.

Go to the Package tab, then browse to the variables collection. Click on the SQL_QUERY variable and view the properties. Turn on Use as Expression. I use notepad to type in the expression, since they didn't put any ... window in there yet. (SP1??) And the expression is...

"Select * from mytable where date = '" + @[User::DATE] + '"

Your query should show up in the variable value property with the correct date at the end.

Voila. This should remove a bunch of useless script from your code. (See Project REAL where they were passing around the XML/A variable to the Analysis Services command task)

I can think of a few other places this would work (connection strings in variables maybe?).

Next goal is to try and figure out why it takes 5-15 seconds just to load a package from DTRUN.