Did you know that it is possible to set expressions on components within the dataflow?
Expressions on data-flow components was virtually the very last feature that was added to SSIS 2005 prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.
Not all components allow you to set their properties using expressions. If they do, then those properties will show up in the properties pane of the dataflow task containing the component. The property is defined using a form of fully-qualified path such as [ComponentName].[PropertyName] or [ComponentName].[ComponentOutput].[ColumnName].[PropertyName].
Here is a little oddity of this feature. Each column in the output of a Derived Column component has a property called [FriendlyExpression] which is the expression that gets applied on that column at execution time. The [FriendlyExpression] property can be set using an expression and herein lies the oddity. The result of the expression has to be, in itself, a valid expression. I can't think of anywhere else in SSIS where this happens.
Expressions on components can be really useful. The best use that I know of is that the SQL command used in a Datareader component can be changed at execution time. The component itself doesn't provide the ability to change the SQL command from a variable like the OLE DB Source component does so putting an expression on the component is the only way to accomplish it.
Any questions on component expressions?
-Jamie