Precedence Constraints in SSIS

Precedence constraints are the connections between the tasks that control the execution order of each task. Precedence constraints are the arrows those use in control flow pane to connect the task.  It is controlling the flow of the based on a task success fail or complete.  The default constraint is success.

The precedence constraint arrows can be different colors to represent different commands (like success, fail, complete etc). We can also write the expression the Constraints.

 

Each colour represents a status of when a task will execute:

Ø  Green = On Success

Ø  Red = On Failure

Ø  Blue = On Completion

Ø  Any color with FX Logo = Expression, or Expression with a Constraint

Let’s see how to use this

Double click on the arrow. Precedence Constraint editor will be open.

 

Evaluation Operation:

 

Constraint

When we set Evaluation operation is Constraint then the value may be

Ø  Success: The precedence executable must run successfully for the constrained executable to run. This is the default value. The precedence constraint is set to green when the Success option is selected.

Ø  Failed: The precedence executable must fail for the constrained executable to run. The precedence constraint is set to red when the Failure option is selected.

Ø  Complete: The constrained executable will run after the precedence executable runs, whether the precedence executable runs successfully or whether it fails. The precedence constraint is set to blue when the Completion option is selected.

 

If you are selecting the Logical AND the arrow is solid.

Expression

When we set the Evaluation operation is Expression

  

Expression will be enabling.

 

Expression is retuning Boolean values (True or False).

 

When we are setting the expression the fx will be displaying on the arrow.

 

Expression and constraint

The precedence constraint is evaluated based on both the Value property and the expression. Both must evaluate to true for the constrained executable to run. I mean first task must be success and the given expression return true then only it evaluate the true. Else it will be failed.

 

Expression or constraint

 The precedence constraint is evaluated based on either the Value property or the expression. At least one of these properties must evaluate to true for the constrained executable to run.

Multiple constraints

  Logical AND

All precedence constraints that point to the constrained executable must evaluate to true in order for that executable to run. This is the default option. If it is selected, the arrow is solid.

Logical OR

Only one precedence constraint that points to the constrained executable must evaluate to true in order for that executable to run. If this option is selected, the arrow is dotted