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 executable 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. If the Constraint Evaluation operation is selected as Expression or Constraint, then both the expression option and Constraint value option are enabled. The following flow charts will explain the possible combinations.
In the above flow chart, Task2 will be executed if the expression is executed successfully or the Task 1 is executed successfully.
In the above flow chart, Task2 will be executed if the expression is executed successfully or the Task 1 is failed as the precedence constraint value is chosen as Failure.
In the above flow chart, Task2 will be executed if the expression is executed successfully or the Task 1 is completed.
Now, to see the precedence with expressions or 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 Evaluation operation as Expression or Constraint as well as the value for the constraint and click on “OK” button.
4. The control flow pane can be as shown in the below. The fx and the green arrow mark between the task 1 and task 2 denotes that task2 will be executed only when the expression is executed and task 1 is successful.
5. The result can be shown in the below picture.
When both the Task 1 fails and expression evaluates to false then only task2 will not be executed as shown in the below picture.
6. If the precedence constraint value is set to Failure and the expression is successful, the execution of task 1 and task 2 will be as follows.
Now when the task 1 fails to execute and expression is successful, the task 2 executes.
In the below case the task1 fails and the expression evaluates to true. Since one of the conditions is true which satisties the OR condition, so the task 2 is executed.
7. If the precedence constraint is chosen as on Completion and the expression as shown in the below picture.
The blue line denotes that the completion of task and the fx denotes the expression in the below picture.
If both the task 1 fails and the expression is executed to false then the result will be as shown in the below picture.
That is it .. Enjoy ..
Thanks Kranthi for the doc . .
Regards,
Roopesh Babu V
