Working with Precedence Constraints in SQL Server Integration Services

Precedence Constraints are useful to define workflow in a sequential manner for various Executables, Containers, tasks under Control tab as well as they also can be used to define the workflow in sequential manner in Event Handlers in SQL Server Integration Services. 

In simple words, Precedence Constraints are useful to perform the data transportation in a sequential manager based on certain business requirements.

you can design the required workflow in your SSIS package by using Precedence Constraint Editor which is a user interface with simple configurable options (Figure 1).

(Figure 1)

As you can see in above screenshot (Figure 1), Precedence Constraints can configure by two options such as Constraint Options and By Logical Operations. We will checkout both of them in detail in the below explanation.

Workflow by Constraint Options

There are four ways to configure this section such as Constraint, Expression, Expression and Constraint, Expression or Constraint.

1. Constraint

If business requirement is to run the next executable only based on Constraint then value can be configured such as Success, Failure and Completion. (Figure 1)

Success: Current executable has to run successfully then only next level executable will start running.

Failure: It will allow to start next execution only when current executable got failed, this constraint maybe useful to capture the log for failure execution.

Completion: In this constraint, next executable has to start running whether previously linked executable was completed successfully or not.

2. Expression

If next executable need to run based on an Expression value for true evaluation then you can select this option. This option is very rare in terms of business requirement but you can define running the next executable based on certain Expression evaluation as well. If you choose this option then you can see in screenshot (Figure 2) that value section has been disabled while a text box has been enabled to write an expression.

(Figure 2)

3. Expression and Constraint

This is most common business scenario in which you select constraint value as Success and evaluation from an expression. It will allow execution of next executable if result of both Constraint and Expression will evaluate to true. When you choose Expression and Constraint option from Precedent Constraint Editor then both the value and expression text boxes would be enabled (Figure 3).

(Figure 3)

4. Expression or Constraint

This option will allow execution of next executable if either of the evaluation is true from the Constraint or from the Expression. At least one of these value must evaluate to true then only next executable will run.

Workflow by Logical Operations

As already mentioned, there are two logical operations are available under Precedence Constraint Editor. Below is the step by step explanation for both of them.

1. Workflow by 'Logical And'

This is the default option selected under Precedence Constraint Editor and green arrow of Precedence Constraint will be solid. If  Logical And is selected then result from all the executable must evaluate to true.

2. Workflow by 'Logical Or'

In some business scenario, you might need to select this option and Precedence Constraint arrow will become as dotted arrow (Data Flow Task, Figure 4). If this option is selected then next level of executable will consider input only from one executable set or container.

For your better understanding, let's take an example (Figure 4) below -

(Figure 4)

There is a business scenario for college (Figure 4), in which one project table will be filled for a particular department which received maximum number of projects submitted for the current day. The basic workflow is defined as below -

1. Check the @RowCount (EST 1) of project table if it has been already filled for a department which received maximum number of projects for the current day.

2. There are two conditions as below -

Condition 1 - If @RowCount is equal to zero then find the find department name from all projects submission which has maximum number of projects submitted for the day (EST 2).

Condition 2 - If @RowCount is greater than zero then truncate the project table (EST 3) and find the department name from all the project submission which has second largest number of project submission for the day.

3. Fill the data into project table using Data Flow Task (DFT 1). Here Data Flow Task will execute on the basis of input value either from the EST 2 or EST 4 (Logical OR, dotted arrows). 

4. There are two Send Mail Tasks for the success and failure activity of Data Flow Task (DFT 1) respectively. Here executable Send Mail Task with green arrow will run only for successful run of Data Flow Task (DFT 1) and executable Send Mail Task with red arrow will run only if Data Flow Task (DFT 1) got failed due to some reason.

5. Start backup utility after the completion of Data Flow Task (DFT 1). Here is the example of Completion constraint which means backup utility has to run for logging purpose even though Data Flow Task (DFT 1) executed successfully or not.

(End of article)

You may also like -

1. Difference between DTS and SQL Server Integration Services 
2. Various configurations available in SSIS