SSIS Precedence Constraints

Introduction

The SSIS control flow defines the workflow for that package. It determines the order in which tasks and containers will execute. In certain condition the tasks will run only when a set of defined conditions are met.

Precedence constraints link the individual tasks together and determine how the workflow moves from one task to another. Precedence constraints are only used with Control flow Tasks and not with Data Flow. The connections between objects within the control flow are done by precedence constraints and in data flow are data path.

As per MSDN

"Precedence constraints link executables, containers, and tasks in packages into a control flow, and specify conditions that determine whether executables run. An executable can be a For Loop, Foreach Loop, or Sequence container; a task; or an event handler. Event handlers also use precedence constraints to link their executables into a control flow.

A precedence constraint links two executables: the precedence executable and the constrained executable. The precedence executable runs before the constrained executable and the execution result of the precedence executable may determine whether the constrained executable runs. The following diagram shows two executables linked by a precedence constraint."

So now we can understand how important the Precedence constraints are. In this article I am trying to discuss about it.

Precedence constraints of Control Flow Tab

Here I provide a pictorial representation of Control flow tabs with different precedence constraint.

In the above, please look that the some lines are solid, some lines are dashed and some have function symbols (Fx).  Each of the line represents the different type of implementation of Precedence constraints.

Solid Line

Here the Solid line represents the logical AND Precedence that means that the tasks will run only when all the constraints condition are met.

Dashed Line

The dashed line represents the logical OR Precedence that means that the tasks will run either of the Precedence constraints condition is met.

Function Symbols

The Function symbols represent the SSIS expression that has been embedded in the constraint for evolutions.

Precedence constraints Editors

Within the control flow we can edit the Precedence constraints by double click the constraint or by right click and then select edit.

When we look at the Precedence constraints editors there are two sections

1.    Constraint Operations

2.    Multi Constraint

Constraint Operation

Constraints are simply the execution status of the upstream tasks.

Evaluation Operators

This dropdown contains

Constraint: 

The precedence constraint is evaluated solely on the option selected in the Value property. For example, if you select Constraint as the Evaluation operation option and select Success as the Value option, the precedence constraint will evaluate to true only if the precedence executable runs successfully. When the precedence constraint evaluates to true, the workflow continues and the constrained executable runs.

Expression: 

The precedence constraint is evaluated based on the expression defined in the Expression text box. If the expression evaluates to true, the workflow continues and the constrained executable runs. If the expression evaluates to false, the constrained executable does not run. Please note that when the Expression option is selected, the Value property is greyed out.

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.

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

If the constraint tasks have multiple constraints, we can choose how the constraint interoperates to control the executions of the constrained tasks.

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 dashed.

ShowAnnotation Property of Precedence constraints

Specify the type of annotation to use. Choose Never to disable annotations, AsNeeded to enable annotation on demand, ConstraintName to automatically annotate using the value of the Name property, ConstraintDescription to automatically annotate using the value of the Description property, and ConstraintOptions to automatically annotate using the values of the Value and Expression properties.

Example to understand the Precedence constraints

Let's take a simple example to understand the Precedence constraints with Expression.

Step-1 [ The Case Study ]

Here we have 3 Tasks Task-A, Task-B and Task-C. The Task-A Execute a SQL Statement and determines that number of records exists in the Table objects named "MY_EMPDETAILS". If there is no records exists the SQL statements return 0. If there is no records found after execution of Task-A, the execution flows moves to Task-C else Task-B. The case study is diagrammatically representing bellow.

Step-2 [ Create the Base Table and Insert Some Records ]

IF OBJECT_ID('MY_EMPDETAILS') IS NOT NULL

BEGIN

  DROP TABLE MY_EMPDETAILS;

END

GO 

CREATE TABLE MY_EMPDETAILS

   ( EMPID    INT          NOT NULL IDENTITY PRIMARY KEY,

     EMPNAME  VARCHAR(50)  NOT NULL,

     DEPT     VARCHAR(25)  NOT NULL);

GO

INSERT INTO MY_EMPDETAILS

       (EMPNAME, DEPT)

VALUES ('JOYDEEP DAS', 'Team Lead'),

       ('SUDIP DAS', 'Manager'),

       ('TUHIN SHINAH', 'Developer'),

       ('SANGRAM JIT', 'SR.Developer'),

       ('DEBAYAN BISHWAS', 'Developer'),            

       ('RUHOOL AMIN SARKAR', 'SR.Developer');

Step-3 [ Open the BIDS and Create the Package Scope Variables ]

The variables of the SSIS have different scope. Here we must create the Package Scope variable and it is important.

To create SSIS variables go to SSIS menu à Variables

Or right click on the work area of control flow tabs and select Variables.

Here I am creating a variable with the name of v_EmpCount with Package level scope.

Step-4 [Designing Control Flow ]

Using three Execute SQL task, design the control flow like bellow

Step-5 [Editing Task-A Execute SQL Statement ]

Here the marked properties are very important and we must provide value on them. In the SQL Statement property write-down the following SQL statement.

SELECT  CONVERT(INT, COUNT(EMPID)) AS TOTALCOUNT

FROM    MY_EMPDETAILS

Step-6 [ Now open the Precedence Constraint Editor ]

Double click the precedence constraint or right click and edit. In the Expression section writes this

@[User::v_EmpCount]>0

Follow the above steps for Task-B and Task-C and other precedence constraint exression write @[User::v_EmpCount] == 0. From the property

Step-7 [ Set the Precedence Constarint propery ]

Execute the Package

Hope you like it.

Posted by: MR. JOYDEEP DAS