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

Figure 10-5 . Expression notation in precedence constraint
It's worth noting that the example in Figure 10-5 shows a non-standard notation on
the constraint. By default, only the function icon (f x ) will appear when you are using
an expression as part of a constraint. Assuming that the expression is not a lengthy one,
I typically change the ShowAnnotation option of the constraint to Con-
straintOptions , which will include the expression itself on the label of the con-
straint. This is an easy reminder of the expression used in the constraint, and it doesn't
require opening the properties window to see the expression.
Task-Level Expressions
In addition to the control flow uses of expressions, most every task and container in
SSIS has its own properties that can be configured using expressions. The options for
configuration using expressions will vary from one executable to the next, but there are
elements common to most tasks and containers:
• Description
• Disable
• DisableValidation
• TransactionOption
• FailPackageOnFailure
• FailParentOnFailure
A common design pattern using a task-level expression is to employ the
SqlStatementSource property of the Execute SQL task. In most cases, you can
use this task combined with query parameters to create dynamic statements in T-SQL.
However, some language constructs (such as subqueries) don't always work well with
parameters, exposing a need to build the SQL string in code. By using an expression
instead of static text for the SqlStatementSource property, the ETL developer
can have complete control of the T-SQL statement when query parameters don't fit.