[Notes from Pinal]: Lots of people think that SSIS is all about arranging various operations together in one logical flow. Well, the understanding is absolutely correct, but the implementation of the same is not as easy as it seems. Lots of people start with lots of enthusiasm, but when it is about building a control flow, which addresses all the necessary elements of the package execution they face issues with the order of the tasks. This is a very critical subject and it requires some expert advice.
Linchpin People are database coaches and wellness experts for a data driven world. In this 21th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to control the flow of the program using SSIS package execution.
In this 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.
In a control flow for SQL Server Integration Services, the “lines” we commonly use to connect tasks to each other are actually smart controls. These 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. 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 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 a 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 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.
Precedence constraints are much more than just lines drawn between two tasks. 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.
If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
