Precedence with CONSTRAINT in SSIS 2008 R2

September 28, 2013

10151

Introduction:

The control flow in a SSIS package defines the workflow for that package. Control flow determine the order in which tasks will run, it also determines under what conditions they are executed. In other words, certain tasks will run only when a set of defined conditions are met. These conditions in a workflow can be configured by using Precedence Constraints. Precedence constraints link the individual executables and determine how the workflow moves from one executable to the next.

More about Precedence Constraints:

A Constraint is a predefined conditional check in the package that can be used to link between control flow tasks. A task will only execute if the condition that is set by the precedence constraint preceding the task is met. By using these constraints, it is possible to ensure different execution paths depending on the success or failure of other tasks. This means that tasks with precedence constraints can be used to determine the workflow of an SSIS package

The Constraint Evaluation operation can have thee possible values, Success, Failure, and Completion. Below flow charts explain the constraints with all possible values.

In the above flow chart, Task 2 will be executed only when the Task 1 is executed successfully. So the constraint value success is represented in a green arrow mark from Task 1 to Task 2.

In the above flow chart, Task 2 will be executed only when the Task 1 is failed to execute. The constraint value failure is represented with a red arrow mark from Task 1 to Task 2.

In the above flow chart, regard less of the Task1 success or failure, Task 2 will be executed upon the completion of Task 1.

Now, to see the precedence constraints in action, follow the below steps.

1) Add two SQL tasks to control flow editor of your project as shown in the below figure.

2) Now for each task, set the valid database connections and write the SQL query that is to be executed for each task as shown in the below figure. To set the database connections and to write SQL query, double click on Task rectangle, the the following window will be opened. Then click on “OK” button.

Similarly set the database connection and write the valid SQL query for Task1 also.

3) Now, a small arrow will be appeared on Execute SQL Task, drag and drop this arrow mark from Execute SQL Task to Execute SQL Task 1. What this does is we are setting the workflow between these two tasks. Now to set the condition checks to determine upon what constraints these two tasks execute, the precedence constraints need to be set. When double clicked on the arrow line, the following window will appear. Set the constraint as well as the value for the constraint and click on “OK” button.

4) When the package is executed, if the first task is executed successfully, then only the workflow allows the second task to be executed as shown in the below figure.

5) If the first task fails to execute, then the second task will not be executed because the precedence constraint value is set to success.

6) If the precedence constraint value is set to Failure, as shown in the below picture, the task 2 will be executed only when the task 1 is failed. If the task 1 is executed successfully, then the task2 will not be executed. These two scenarios are shown in the following pictures.

7) As the precedence constraint value is set to Failure, the arrow mark is set to red as shown in the picture, the task 2 will be executed only when the task 1 fails.

8) If the precedence constraint value is set to Completion as shown in the below picture, the arrow mark from task 1 to task 2 will turn to blue color indicating that, irrespective of task1 success or failure, task 2 will be executed upon completion of task 1. This can be shown in following pictures. Set the precedence constraint value to Completion.