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鈥檚 class we鈥檙e 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 (). I鈥檒l briefly cover each of the constraint types in an abbreviated list and then we鈥檒l 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鈥檚 execution results in a failure. Visually indicated in control flow by a solid red line.
- Completion 鈥� Workflow will proceed when the preceding container鈥檚 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 鈥榝x鈥� 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 鈥榝x鈥� 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 鈥楽how 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 .
Constraints in Action
Now that we鈥檝e seen the different constraint types, let鈥檚 examine how they鈥檙e 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鈥檝e used an empty script task as an 鈥渁nchor鈥� 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鈥檙e going to look at the constraint leading to the sequence containers for the Supplier table. We鈥檝e used and Expression and Constraint here and chosen the value for Success. We鈥檙e 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 鈥楶arent_TblName鈥� is set to 鈥渟upplier鈥�. Once both of these situations evaluate as being true, we will execute this container.
Now that we鈥檙e inside our sequence container, we have another set of constraints. Once again we鈥檙e using an empty script task as an 鈥渁nchor鈥� for our precedence constraints. This time we鈥檝e 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鈥檝e created another variable on this package called 鈥楢ppendFlag鈥� 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鈥檚 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 鈥楢ppendFlag鈥� variable. In order for us to go down this workflow both value of 鈥楢ppendFlag鈥� 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鈥檝e set the precedence constraint to look for the value of 鈥楢ppendFlag鈥� to be true. In this path, however, we鈥檝e 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.
