Welcome back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the two in order to complete a unit of work. In today’s class we’re going to continue exploring that data load package and take a look at another critical piece of SSIS that you should know about: precedence constraints.
So what exactly are precedence constraints? They are the connectors that link together tasks in the Control Flow, and they define the workflow of your 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.
Constraint Types
You can have different types of constraints between tasks. You can read more about constraints in detail from MSDN article (Link). I’ll briefly cover each of the constraint types in an abbreviated list and then we’ll discuss how we used these constraints in our parent-child package from our previous lesson.
- Success – Workflow will proceed when the preceding container executes successfully. Visually indicated in control flow by a solid green line.
- Failure – Workflow will proceed when the preceding container’s execution results in a failure. Visually indicated in control flow by a solid red line.
- Completion – Workflow will proceed when the preceding container’s execution completes, regardless of success or failure. Visually indicated in control flow by a solid blue line.
- Expression/Constraint with Logical AND – Workflow 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).
Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip.
Constraints in Action
Now that we’ve seen the different constraint types, let’s examine how they’re used in conjunction with parent-child package setup. Our first use of constraints comes at the top of the child package from the script task to the sequence containers. We’ve used an empty script task as an “anchor” task. This is used as a starting point to continue on to the corresponding workflow. As we learned in our last class, we have a variable being passed from our parent package with the value of the name of the file we are currently processing.
In this first example, we’re going to look at the constraint leading to the sequence containers for the Supplier table. We’ve used and Expression and Constraint here and chosen the value for Success. We’re also looking at the value of the variable being passed to the child package. For this particular workflow, we are waiting until the value of the variable ‘Parent_TblName’ is set to “supplier”. Once both of these situations evaluate as being true, we will execute this container.
Now that we’re inside our sequence container, we have another set of constraints. Once again we’re using an empty script task as an “anchor” for our precedence constraints. This time we’ve got two possible paths to go down. The first is to execute an Execute SQL task. This task checks for the existence of the table (in this case, the supplier table). If it exists it will drop the table and then recreate it. The other path leads directly to a data flow task which simply loads the table specified from the flat file.
I’ve created another variable on this package called ‘AppendFlag’ which is a boolean value. The purpose of this flag is so that you can choose to load the tables with a fresh load from the flat file (the Execute SQL task path) or you can simply append an already existing table’s data with data (data flow path). The default value of the variable is false.
The first path to the Execute SQL task uses an expression and constraint which is looking at the value of the ‘AppendFlag’ variable. In order for us to go down this workflow both value of ‘AppendFlag’ must be false AND the previous component executed successfully. The other path from the script task leads directly to the data flow task which actually loads the table. For this path, I’ve set the precedence constraint to look for the value of ‘AppendFlag’ to be true. In this path, however, we’ve chosen to use a logical OR. The reason for this being that the Execute SQL task, once complete, also leads to the data flow task. Due to the data flow having two different input paths, we must use the logical OR (if you try to choose logical AND, BIDS will quickly yell at you).
Conclusion
When we bring it all together, we now have a parent-child package that passes variable values. These values are used to execute specific workflows based on the value of the variable passed. Precedence constraints are an extremely helpful and invaluable tool in your SSIS toolkit. Using precedence constraints can help you create very dynamic workflows within your packages.
