In SSIS, tasks are linked by precedence constraints. If there are two tasks and you want them to execute in a certain order, this is where precedence constraints are used to define the order in which they get executed in the control flow. The control flow in a SQL Server Integration Services (SSIS) package defines the workflow for that package. In this post, we are going to look at how to define precedence constraints using BIML.
What is Precedence Constraints in SSIS ?
Precedence Constraints are the connectors that link together tasks in the Control Flow, and they define the workflow of your SSIS package. When two tasks are tied together with a constraint, the destination task will execute based on two things: The final state of the task that precedes it and special rules you can define on the constraint using specialized expressions.
Please find below the list of constraints types:
- Success : Task will proceed when the preceding task executes successfully. Visually indicated as a solid green line.
- Failure : Task will proceed when the preceding task fails. Visually indicated as a solid red line.
- Completion : Task will proceed when the preceding task completed regardless of failure or success of the task. Visually indicated as solid blue line.
- Expression/Constraint with Logical AND – Task will proceed when specified expression and constraints evaluate to true. Visually indicated in control flow by a solid color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).
- Expression/Constraint with Logical OR – Workflow will proceed when either the specified expression or the logical constraint (success/failure/completion) evaluates to true. Visually indicated in control flow by a dotted color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).
One feature of BIML is the Constraint Mode property which is part of Packages and Containers. This property controls how precedence constraints are generated in the control flow. There two types of Constraint Mode property: Linear and Parallel. Below I will show examples of both and their BIML respective code:
First we will look at the Constrain Mode and then dig into how to write BIML code for adding precedence constraints to the tasks.
1. Constraint Mode — Linear :
The following BIML code set the Constraint Mode to Linear as shown in Figure 1:
Figure 1 : Shows constraintmode set to Linear
The BIML above code results in a package as shown in Figure 2.
Figure 2 : Result after generating SSIS Packages using BIML code in Figure 1
2. Constraint Mode — Parallel :
The following BIML code sets the Constraint Mode to Parallel as shown in Figure 3:
Figure 3 : Shows constraintmode set to Parallel
The BIML above code results in a package as shown in Figure 4.
Figure 4 : Result after generating SSIS Packages using BIML code (ConstraintMode = Parallel)
Once the SSIS Package is in Parallel Mode, you can start adding precedence constraints. In this example I will show the below exercise and how it is done in BIML:
1. If Data Flow Task Name – “DFT – Load Staging Data” executes successfully go to Data Flow Task Name – “DFT – Cleanse Staging Data”.
2. If Data Flow Task Name – “DFT – Cleanse Staging Data” executes successfully go to Data Flow Task Name – “DFT – Load into DataWarehouse”.
3. If any of the Data Flow Task – “DFT – Load Staging Data” , “DFT – Cleanse Staging Data” and “DFT – Load into DataWarehouse” failes go to Data Flow Task – “DFT – Error Rows”.
The BIML code for the above requirement is shown in figure 5:
Figure 5: BIML code to add precedence constraints (Success and Failure Constraints).
Above BIML code will result into the SSIS Package as shown in figure 6
Figure 6 : SSIS Package generated using the BIML file showing the success and failure precedence constraints.
We can use the Precedence Constraints collection on each task. You just add an input to the collection, and reference the output of the task that should execute prior to this one. You can reference it using TaskName.Output. (DFT – Load Staging Data.Output).
There is a lot of control when using Precedence Constraints. You can also specify an expression to add more flexibility to the control flow.
Happy Coding…BIML 🙂