I have extolled the virtues of the expression language in SSIS more times than I care to remember and quite frankly regular readers of this blog must be fed up of me banging on about them. Well I make no excuse for it - property expressions dramatically increase the power of your SSIS packages - turning them into dynamic behemoths rather than little static workflows.
What I always thought would be nice though would be to have the ability to view the results of the expressions at debug time in the VS watch window. Well, whilst writing my previous blog post on Splitting a file into multiple files I devised a new strategy towards using expressions that I thought was worth sharing. Its incredibly simple as well.
Instead of putting expressions on object properties, put the expression into the 'Expression' property of a variable and set the object property expression to be the value of the variable instead. There are 3 benefits to doing this:
1) You can view the value of the expression in the watch window
2) You can leverage the OnVariableValueChanged event
3) Expressions can be shared by different object properties.
There you go. Three good reasons to put your expressions in variables rather than straight into a property expression.
Here's a screenshot from my aforementioned data splitting demo that shows the usefulness of being able to watch variable values in the watch window:
-Jamie
P.S. Thanks to Nick Barclay for his comment here: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/2458.aspx#2459 that prompted me to write this post!
UPDATE: Another advantage of this approach is that you can raise OnInformation events containing the dynamic SQL. This means that the SQL statement will get logged by whatever log provider you are using.