SSIS Precedence Constraints

In the control flow for SQL Server Integration Services, the “lines” we commonly use to connect tasks to each other are actually smart controls. These SSIS precedence constraints allow the ETL developer to customize the program flow from one task to another. The most common use of precedence constraints is to simply connect two tasks with the default constraint, which is the success constraint. In this usage, the downstream task will be executed if and only if the execution of the upstream task is successful.

In this SSIS Basics post, I’m going to review the essentials of precedence constraints in SQL Server Integration Services, and will demonstrate how these can be used to precisely control the flow of the program during the SSIS package execution.

Overview of SSIS Precedence Constraints

As shown in the example below, the task DFT Write data to output table will be executed if the preceding task named SQL Truncate output table is successfully executed. Similarly, the final task in the chain, named SQL Log row count to audit tbl, will be executed only if the preceding data flow task was successful.

The green lines connecting these tasks are the precedence constraints. In the above example, we know that these are success precedence constraints by their color.

Using SSIS precedence constraints for success operations is not the only option we have. Because these are smart controls, we can configure them in a variety of ways for maximum flexibility. An example of this would be use a failure precedence constraint to code for the possibility of failure. In this scenario, we could create one or more tasks that would be executed in the event of a failure within a package. As shown below, we have a snippet of a package showing a failure path from the data flow by way of a failure precedence constraint.

In this example, we connect an SSIS precedence constraint from the data flow task to the task named SQL Remove data from partial load, but instead of using the default setting, we reconfigure this as a failure constraint (shown with the “Failure” label and red color) to redirect the execution flow to that task in the event that the data flow execution fails. Downstream from there, we have yet another precedence constraint configured as a completion constraint (indicated by the label and the black color). This constraint indicates that the task SQL Log failure event to event log will be executed whenever the upstream task is finished executing, irrespective of whether the result of that execution was success or failure.

To configure a precedence constraint to a setting other than the default value of success, simply double-click on the precedence constraint and set the appropriate setting, the options for which are shown below.

As shown, we can choose Success (the default setting), Failure, or Completion.

Note that there are a few other options as well. Above the selection for the type of constraint, you’ll see that we can also choose the evaluation operation. The default setting is Constraint, which means that the precedence constraint uses only the success/failure/completion results as described above. However, we also have some other options. We can use an SSIS expression along with, or perhaps instead of, the execution status evaluation to determine how to control the execution flow.

Additionally, we have the ability to control the behavior when multiple constraints are used. By default, if multiple SSIS precedence constraints are connected to a single downstream task, then all of those constraints must evaluate true for said downstream task to execute. However, we can override that default behavior by setting this value for multiple constraints to Logical OR, indicating that the downstream task will be executed if any of the connected constraints evaluates true.

As shown in the example below, we have modified the original example, changing both the evaluation operation as well as the multiple constraint behavior. On the constraint between the data flow task and the data deletion task, we add an SSIS expression to check the row count check to confirm whether any rows were loaded as part of the failed execution (since we wouldn’t need to run the delete operation if no rows were loaded). Further, we’ve added a completion constraint between the data flow task and the task named SQL Log failure event to event log and set the multiple constraint behavior to Logical OR to make sure we log the failure whether or not the delete operation occurred.

We get visual reminders of these settings as well. The function indicator (which looks like an italicized fx) appears on the precedence constraint on which we are using the expression. For the Logical OR setting, the constraints will appear as dashed lines rather than solid lines.

Conclusion

SSIS precedence constraints are much more than just lines drawn between two tasks to define execution order. In fact, these are powerful and flexible decision tools that allow the ETL developer a great deal of control over the flow of the SSIS execution.