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

these shared connections are common to all packages in the project. As such, any prop-
erty setting on these project connection managers—including the use of expres-
sions—would be immediately reflected in all packages in the project. This is a wel-
come and much needed improvement to the way packages interact with one another,
but for those of us who have worked with previous versions of SSIS, it's a bit of a
paradigm shift. Don't get caught off guard when an expression applied to a project con-
nection in one package gets applied to the other packages in the project!
Control Flow
Within the control flow, there are a couple of different ways to implement SSIS expres-
sions. First, each of the tasks and containers will expose several properties that are con-
figurable using expressions. In addition, the paths between them (known as precedence
constraints ) allow ETL developers to customize the decision path when moving from
one task/container to another.
Conditional Execution Through Expressions and Con-
straints
The essential function of the control flow is to manage the execution of package ele-
ments. By using precedence constraints, you can design a package so that tasks and
containers are fired in the proper order and with the correct dependencies intact. For a
simple example of this, think about a package that truncates and then loads a staging
table. You can perform both of these tasks in the same package, but without a preced-
ence constraint to cause the insert operation to occur after the TRUNCATE TABLE ex-
ecution, you run the risk of inadvertently loading and then deleting the same data.
You can configure precedence constraints to manage flow-based successful com-
pletion of the preceding task (the default behavior), or you may set them to cause the
task to execute only if the preceding task fails. In addition, you can set the constraint to
Completion , allowing the downstream task to fire when the upstream task is fin-
ished, regardless of its outcome. Tasks may have multiple precedence constraints, and
you may set these so that any or all of them must be satisfied before the task to which
they are attached will execute. Figure 10-3 shows a fairly typical use of precedence
constraints; note that the unlabeled arrows represent Success constraints, and the
others are labeled as to their purpose. The dashed lines indicate that the task is con-
figured to execute upon completion of either of the preceding tasks.