Former Integration Services developer Michael Entin recently responded to an interesting question on the internal SSIS discussion list at Microsoft. The user was asking: How can I assign the expression [in this case, for the Derived Column transformation] dynamically at run time?
The intuitive but less-than-obvious answer is...use an expression to set the expression! This lets you put your expression in a string variable such as @User::DerivationRule, and assign that by using an expression to the FriendlyExpression property that you want to set.
Remember that you find expressionable properties for data flow components on the containing Data Flow task, and not on the individual component.
Let's walk through the scenario.
- User wants to create a Derived Column but assign the formula that determines the column's value at run time.
- Go to properties of containing Data Flow task, Expressions, and open Property Expressions Editor.
- Select and edit FriendlyExpression property of appropriate property. For example:
If you're interested in the possibilities here, you may want to glance at the BOL topic:
Data Flow Properties that Can Be Set by Using Expressions
-Doug