Using Precedence Constraints In SSIS

Precedence constraints are the green, red, and grey connectors in the Control Flow that link the tasks together and can be used to manage the workflow of a package and handle error conditions.

NOTE: Be aware that grey precedence constraints look a lot like data paths in the Data Flow, but they are much different. On the one hand, precedence constraints define what tasks should be executed in which order; on the other hand, Data Flow paths define to which transformations and destinations data should be routed. Data Flow paths deal with moving data; precedence constraints deal with workflow handling.

Precedence Constraints

Precedence Constraint Basics

The main purpose of precedence constraints is to control when tasks and containers should run in relation to one another. This revolves around whether tasks succeed (green), fail (red), or just complete with either a success or a failure (grey). Precedence constraints can also be more granularly controlled through advanced properties, which are addressed in the next section.

Learn how to use SSIS, from beginner basics to advanced techniques. Enroll for Free "SSIS Training" Demo!

The below screenshot shows a typical example. If the Initial Data Flow Task completes successfully, the Success Data Flow Task will execute. A green arrow (on the left) points to the Success Data Flow Task. If the Initial Data Flow Task fails, the Failure Send Mail Task executes, sending notification of the failure. A red arrow (in the middle) points to the Failure Send Mail Task. No matter what happens, the Completion Script Task will always execute. A grey arrow (on the right) points to the Completion Script Task.

When you are initially connecting two tasks, you’ll see that the default precedence constraint is a green arrow designating success. To change how the precedence constraint is evaluated, you can right-click the arrow and choose a different outcome from the pop-up menu, as shown in the below screenshot.

You can also combine tasks into groups by using containers, in which case the workflow can be controlled by the success or failure of the container. For example, a package may have several Data Flow Tasks that run in parallel, each loading data from a different source. All tasks must complete successfully for the container to determine success and allow the flow to continue to the next step. A Sequence Container contains the tasks, and the precedence constraint is drawn from the container to the next step. The example shown in the below screenshot illustrates how a Sequence Container might be used. After the Initialization Script runs, the Import Data Container executes. Within it, three Data Flow processes run in parallel. A failure of any of the Data Flow Tasks will cause the Import Data Container to fail, and the failure message will be sent. If all three complete successfully, the Clean Up Script will run.

Advanced Precedence Constraints and Expressions

While using a standard constraint evaluation in a precedence constraint provides an impressive way of managing the execution of certain tasks, the precedence constraint provides even more flexibility to manage the flow of the package. Using the advanced functionality of SSIS expressions inside of the precedence constraint, you can dynamically determine whether the next task or container should run at package runtime. You can use the dynamic functionality to run certain tasks based on different criteria, such as running only on Sundays, not running after 2:00 a.m., or running if an external parameter flag is set to True. This allows you to modify the Control Flow in any way required.

 

Check Out SSIS Tutorials

 

Not only can precedence constraints be configured to evaluate Boolean expressions, but they can also be combined with other precedence constraints through a logical OR evaluation. The advanced precedence constraints are defined through the Precedence Constraint Editor dialog, shown in the below screenshot. To pull up the editor, either double-click the precedence constraint arrow or right-click the arrow and choose Edit.

Using Boolean Expressions with Precedence Constraints

With the editor, you can control the workflow within a package by using Boolean expressions in place of, or in addition to, the outcome of the initial task or container. Any expression that can be evaluated to True or False can be used. For example, the value of a variable that changes as the package executes can be compared to a constant. If the comparison resolves to True, then the connected task executes. You can base the evaluation of a precedence constraint on both the outcome of the initial task and an expression. This enables the SSIS developer to finely tune the workflow of a package. The following table shows the four Evaluation Operation options contained in the dropdown menu for configuring a precedence constraint:

In the precedence constraint configuration, you can choose which type of Evaluation Operation to use and set the value of the constraint and/or supply the expression.

In the following example, you will simulate flipping a coin to learn more about using expressions with precedence constraints. The Coin Toss Example uses Execute SQL Tasks, Script Tasks, and For Loop Containers, as well as advanced precedence constraint logic, to create the replica. The final package you will create is shown in below screenshot. 

2. Create a new SSIS project in SQL Server Data Tools and create a new package.
3. Create an OLE DB Connection Manager pointing to the AdventureWorks database where the CoinToss table was created. The steps for creating a Connection Manager are covered in SSIS Tasks Topic.
4. To reset the value of the coin toss each time you run the package, add an Execute SQL Task named Clear Results to the Control Flow design area.
5. Double-click the Clear Results Task to open the Execute SQL Task Editor.
6. Set the Connection property to point to the AdventureWorks Connection Manager and type the following code in the SQLStatement field:
UPDATE CoinToss
SET Tails = 0, Heads = 0
Now that the package is prepared, it is time to set up the simulator.
7. Right-click the Control Flow design area and select Variables from the pop-up menu to open the Variables window.
8. Create a new package level variable called Result with a data type of Int32 to hold the “result” of the coin toss.
9. To toss the coin multiple times, you will use a For Loop Container. Add the container to the design area, and name it Coin Toss Simulator. Connect the precedence constraint from the Clear Results Task to the Coin Toss Simulator.
10. With the Coin Toss Simulator selected, open the Variables window to add a variable called Count with a data type of Int32. In this case, the variable will be used only by the For Loop Container, so the scope can be the Coin Toss Simulator.
11. You can set the properties of the Coin Toss Simulator Container by opening the editor. Set the properties as shown in the following table and click OK:

This should look familiar to you if you have programmed in almost any language: The For Loop Container will execute whatever is inside of it 100 times.
12. In this case, drag a Script Task named Toss into the Coin Toss Simulator to perform the coin toss.
13. To set up the code to simulate the coin toss, double-click the Toss Script Task to open the Script Task Editor.
14. In the Script pane, select the ScriptLanguage of Microsoft Visual Basic 2012 and select the variable User::Result in the ReadWriteVariables property. The script will have access only to variables set up in this way.
15. Click Edit Script to open the Visual Studio Tools for an Applications design environment. Each time this script runs, it will randomly pick a value of one or two and store the value in the Result variable. Replace Public Sub Main with this code:

Public Sub Main()
Randomize()
Dts.Variables(“User::Result”).Value = CInt(Int((2 * Rnd()) + 1))
Dts.TaskResult = ScriptResults.Success
End SubClose out of the script editor and then click OK in the Script Task editor to close it and return to SSDT.
16. Set up the outcome tasks for the coin toss. Drag two Execute SQL Tasks into the Coin Toss Simulator Container, with one named Heads and the other Tails. Connect the Toss Script Task to each of the Execute SQL Tasks.
17. you need to change the precedence constraints so that the control will pass to the appropriate tasks.
18. Double-click the precedence constraint pointing to Heads to bring up the Precedence Constraint Editor. Change the Evaluation Operation from Constraint to Expression. As shown in below screenshot, the Expression text box will become available, enabling you to type the following into the Expression property

@Result == 1The precedence constraint will have an fx symbol next to it specifying that it uses an expression.

NOTE When evaluating two values in an SSIS Boolean expression, you need to use two equals signs (==). This indicates that the expression returns TRUE or FALSE depending on whether the values are equal. NULL values do not evaluate, so be sure to ensure that both sides of the == return an actual value. The only time you use a single equals sign is when you are using an SSIS expression or the Expression Task to set the value of a variable, such as inside of a For Loop Container.
19. To continue with the example, open the properties of the precedence constraint that is connected to Tails. Change the Evaluation Operation from Constraint to Expression, and type this in the Expression property:@Result == 2
20. Click OK to accept the properties. Just a couple more details and you’ll be ready to run the package!
21. Double-click Heads to open the Execute SQL Task Editor. In the Connection property, set the value to the AdventureWorks Connection Manager. Type the following code in the SQLStatement property to increment the count in the CoinToss table:

UPDATE CoinToss SET Heads = Heads + 1
22. Click OK to accept the changes.
23. Bring up the Execute SQL Task Editor for the Tails object. Set the Connection property to the AdventureWorks Connection Manager. Type this code in the SQLStatement property:

UPDATE CoinToss SET Tails = Tails + 1
24. Click OK to accept the configuration and run the package.
 As the package runs, you can see that sometimes Heads will execute, and sometimes Tails will execute.
25. Once the package execution completes, return to SQL Server Management Studio to view the results by running this query: SELECT * FROM CoinTossSimilar to a real coin toss, Heads will come up approximately 50 out of 100 times.

 

Frequently Asked SSIS Interview Questions & Answers

 

This simple example demonstrates how to use an expression to control the package workflow, instead of or combined with the outcome of a task. In a business application, the precedence constraint might be used to ensure that the number of rows affected by a previous step is less than a certain value, or maybe a task should execute only if it is a particular day of the week. Any variable within scope can be used, and several functions and operators are available to build the expression. Any valid expression will work as long as it evaluates to True or False. See Using Variables, Parameters, and Expressions Topic to learn more about building and using expressions.

Working with Multiple Precedence Constraints

In your package workflow, you can have multiple precedence constraints pointing to the same task. By default, the conditions of both must be True to enable the execution of the constrained task. You also have the option of running a task if at least one of the conditions is True by setting the Multiple constraints option to “Logical OR. One constraint must evaluate to True,” as shown in the below screenshot.

The solid precedence constraints change to dashed precedence constraints when the Logical OR option is chosen. You only need to select Logically OR for one constraint, as all other constraints that point to the same task or container will also change to Logical OR. The below screenshot shows how the Send Mail Task named Error Message will execute if either of the Import Data Flow Tasks fails. In this example, both precedence constraints are configured to fail but the Logical OR has been set instead of the Logical AND. Because the Logical OR has been enabled, the precedence constraints are dashed lines. Below screenshot shows the Import Customers Data Flow is successful (the circle with a checkmark), but the Import Orders Data Flow failed (the circle with an X). Although not shown in the picture, the successful circle will be green and the failure circle will be red. Because one of the Data Flows failed, the Error Message Send Mail Task is executing. If both Data Flows had been successful, the Error Message Task would not have run.

Combining Expressions and Multiple Precedence Constraints

In the next example, an expression is evaluated in addition to using multiple constraints. This workflow loads data from a series of files into a SQL database. The business rules require that no file can be loaded into the database more than once, and the files must be archived, whether they have been loaded previously or not. The below screenshot shows the workflow with the required business rules implemented.

In this workflow, a Foreach Loop Container is used to loop through the files you want to load into the database. With each iteration of the loop, the filename is assigned to a variable, which is then used in the first task (the Execute SQL Task) to determine whether it has been previously loaded. The Execute SQL Task called Check Log Table will use the variable that holds the current filename as an input parameter to a SQL statement to determine whether it does in fact exist in the table. The result of the query will return either true or false, to be stored in a variable that is called User::blnFlag. This task is pivotal in that it is the basis for the evaluation within the precedence constraints. Double-clicking the precedence constraint connecting the Check Log Table Execute SQL Task to the Process Files Data Flow Task will display the dialog shown in below screenshot. 

The properties of the precedence constraint in the above screenshot are set to allow the workflow to pass through to the next task (the Process Files Data Flow Task) if the previous step succeeded and the expression has evaluated to false. This constraint is essentially asking, “Was the previous step successful and is this a new file that has not been previously loaded?”

Now that you have determined the business rule behind that constraint, double-click the dashed precedence constraint that connects the Check Log Table Execute SQL Task and the Move to Archive File System Task. The dialog presented for this constraint is shown in below screenshot.

The properties of the precedence constraint in the below screenshot represent a couple of pieces of business rule logic. First, the evaluation operation is set to Expression and Constraint. Second, the expression is testing whether your variable @[User::blnFlag] is true. The interpretation of this expression is asking, “Has the current file been previously loaded?” Lastly, the Logical OR radio button is selected to facilitate an OR condition between your two precedence constraints. In plain English, the properties that are defined for the two precedence constraints will allow the file to be processed and archived if it has or has not been previously loaded.

By using precedence constraints, you control the order of events within a package. After a task or container executes and depending on how the precedence constraint between the two components was evaluated, the second task or container runs. With all of these options, you can control the workflow of your package at a very granular level. The great thing about precedence constraints in SSIS is that they give you the flexibility to implement complex business rules like the scenario previously demonstrated. Drilling down a bit more, you will next learn another way to control package execution: event handling.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator