Expression Language Patterns - SQL Server Integration Services Design Patterns - page 288

parameters for its use. Unless there's some business case or regulation dictating other-
wise, it's a better long-term solution to externalize these values rather than rely on ex-
pressions.
Variable Expressions
As shown in Figure 10-1 , you can configure each variable with a static value in the
Value field or define a value expression that will be evaluated at runtime. Note that the
variable window was improved starting in SQL Server 2012—in older versions, static
values were shown in the Variable window, but you had to use the Properties window
to view or alter an expression for a variable.
Figure 10-1 . Expressions with variables
In practice, I often see expressions applied to variable values, and then the resulting
variable is used as a property on a task or component (as opposed to it being used as an
expression to set the property directly). I'm a fan of this design pattern for one simple
reason: reusability. It's not uncommon for components to share certain properties, and
building expressions on each of those shared properties for every applicable component
is both redundant and unnecessary. For those properties that will be shared across mul-
tiple tasks or components, it's far easier to centralize the expression logic into a vari-
able and then use that variable to set the shared properties. This approach allows for
faster development as well as easier maintenance should the logic require changes
down the road.
When using this design pattern, don't forget that you can also “stack” variable val-
ues. In the expression statement, you can leverage other variables to set the value of the
current variable.
Connection Managers
One of the most practical and common places to use SSIS expressions is the Connec-
tion Managers tray. Generally speaking, it's typically preferable to store dynamic con-