Rajendra Gupta

Overview of SSIS Precedence Constraints

December 3, 2019 by

This article explores the SSIS Precedence Constraints, along with examples and scenarios.

Introduction

Microsoft SQL Server provides integration services for building SSIS packages to design complex solutions, workflows, data migration, and transformations. Once you launch Visual Studio for an SSIS package, you can see the following components:

  • Control flow: We define the task in the control flow, such as data flow task, Execute SQL task, FTP task
  • Data flow: Data flow defines the source and destination data flow, and we define transformation as well in this tab
  • Parameters: We can manage SSIS variables using this tab
  • Event handlers: It allows defining the event-handling mechanism in the SSIS package
  • Package explorers: Here, we can explore the SSIS package connections, log providers, variables and parameters

SSIS package explorer

Overview of SSIS Precedence Constraints

In the control flow, we can define multiple workflows, tasks in a single SSIS package. We do not want all the tasks to execute at the same time. Control flow also allows defining the order in which the task will run.

Let’s add two execute SQL tasks in the control flow area and configure them to execute the T-SQL statements.

Note: You can refer to SSIS articles for understanding SSIS configurations.

Control flow tasks

I require executing the tasks with the following conditions:

  • Start package execution with SQL task 1
  • If the SQL task 1 fails, then execute task 2

If we execute the package without any additional configuration, it executes both the tasks in parallel:

Execute tasks

We use SSIS precedence constraints to define the program flow from one task to another. We can see a green color arrow in the following image that defines precedence constraints:

Green color connector

We can see green, red, and grey connectors in the Control Flow to link tasks together. The standard and default constraints is a success constraint. Drag an arrow from SQL task 1 and drop it on SQL task 2 to build a success precedence constraint.

In the following image, the SQL task 2 executes if and only if the execution of SQL task 1 is successful:

success constraint

We can change the precedence constraint from success to failure or completion. Right-click on the arrow and choose different precedence, as shown in the following screenshot:

Constraints options

Let’s change it to failure and arrow colour changes to Red. It also shows an annotation for failures.

Failure constraint

In this scenario, the package executes in the following manner:

  • Start package execution with SQL task 1
  • If the SQL task 1 fails, then only execute task 2

In the following screenshot, we can see that SSIS does not execute the task 2 because task 1 status is successful:

Failed constraint execution

Modify the package so that SQL task 1 fails and execute it again. Now, it executes the task 2 as defined in the failure condition of SQL task 1:

Output of failed conditions

Another SSIS precedence constraint is completion. Sometimes, we want to execute another task even first task execution status is a success or fail:

Completion Constraints

In the following screenshot, executes SQL task 2 is successful despite task 1 failure:

package execution of completed constraint

It also executes task 2 if SQL task 1 is successful:

Multiple tasks execution with precedence constraint

We have explored the basics of SSIS Precedence Constraints in the SSIS package. Let’s have a summary theoretically and explore it further.

As highlighted above, we have the following precedence constraints in SSIS.

First, let me give brief information about the components involved in SSIS precedence constraints:

  • Precedence Executable: It is the source from where we define the precedence constraints
  • Constrained Executable: It is the destination task, and its execution depends on the defined precedence constraints and outcome of precedence executable

Precedence Executable

Double-click on precedence, and it opens the precedence constraints editor:

precedence constraints editor

In this editor, we have the following SSIS Precedence Constraints options:

1. Constraints

Here, we define the precedence constraints as defined above. We have the following precedence constraints options available.

Success

In this constraint, precedence executable must execute successfully so that the constrained executables can execute. The color of success precedence is green.

Failure

In this constraint, precedence executable must fail so that the constrained executable can execute. Its color is red.

Completion

It runs the constrained executable regardless of the state of precedence executable task. The color for completion precedence constraint is blue.

We can define multiple SSIS precedence constraints for a single task in the SSIS package. In the following screenshot, all three precedence constraints with executing SQL task 1:

all three precedence constraints

2. Expression

If we choose expressions in SSIS precedence constraints, it evaluates the expression and if the expression is true, then the constrained executable runs.

For example, in the following screenshot, we defined an expression for checking the weekday as per the following example:

DATEPART(“weekday”,GETDATE()) == 1

Expression

Click on Test, and it evaluates the expression:

Validation test

Click OK, and it changes the expression symbol fx as shown below:

precedence symbol for expression

It runs the constrained executable if the evaluated expression is true:

evaluate expression as true

If the expression returns false condition, it doesn’t execute the constrained executable:

evaluate expression as False

3. Expressions and Constraint

In this mode, the SSIS package evaluates both precedence constraint and expression. The output of both must be true so that constrained executable can run.

For example, open the SSIS precedence constraint editor and change the options as follows:

  • Evaluation operation: Expression and Constraint
  • Value: Success
  • Expression: DATEPART(“weekday”,GETDATE()) == 2

Expressions and Constraint

Click OK, and it looks as per the following screenshot:

Expressions and Constraint package execution

Execute the package, and we see that it did not run the constrained executable. Upon investigation, we find the following facts:

  • Precedence constraint returns true because execute SQL task 1 is a success
  • The expression does not meet the criteria and returns a false value
  • The combination of precedence constraints and expressions return false, and it does not execute the constrained executable

Output of expression precedence constraint in SSIS

Let’s change the expression so that it returns true, and it executes the constrained executable.

  • Precedence constraint returns true because execute SQL task 1 is a success
  • Expression meets the criteria and returns a true value
  • The combination of precedence constraints and expressions return true, and it executes the constrained executable as well

output of expression constraint

Similarly, we can change it to failure precedence constraint and expression:

failure precedence constraint and expression

4. Expressions or Constraint

In this option, at least one of the SSIS precedence constraint or expression should be true. Looking at the above example, change the SSIS precedence constraints editor to reflect this change:

Expressions or Constraint

Execute the SSIS package and note the difference:

  • SSIS Precedence constraint returns true because execute SQL task 1 is success
  • The expression does not meet the criteria and returns a false value
  • We have one true value in the above conditions, and it returns true value. It executes the constrained executable as well

Expressions or Constraint output

Multiple SSIS Precedence Constraints

Let’s make things more complicated. Add one more execute SQL task and connect it to SQL task 2:

Multiple Precedence Constraints

Now execute SQL task contains two precedences defined on it:

  • Precedence constraint from SQL task 1
  • Precedence constraint from SQL task 3

Intentionally, I have made a syntax error in SQL task 3 so that it should fail. Execute the SSIS package, and You see the following component status.

  • Execute SQL task 1 status is a success
  • Execute SQL task 3 status is fail

We cannot see the execution of SQL task 2 while it should execute because of success precedence constraint from task 1:

behaviour of Multiple Precedence Constraints

However, it did not execute the constrained executable. Let’s investigate the reason for it. Open the precedence constraint editor, and you can see the following property for multiple constraints:

Multiple Constraint property

If we have multiple constraints for a task, this property controls the behavior for the constrained executable execution.

By default, all the constraints must evaluate to True. Due to this, we did not see the execution of SQL task 2 because one of the precedence constraints is false (failure of SQL task 3).

Let’s change the value of multiple constraint property-Logical OR– One constraint must evaluate to True:

Logical OR condition

Click OK, and you can note the changes in SSIS precedence constraints lines. It changes lines from solid to dotted lines:

Logical OR condition and dotted lines

Execute the SSIS package, and we can see the following:

  • Execute SQL task 1 status is a success
  • Execute SQL task 3 status is fail
  • Logical OR of these two precedence constraints returns true, and it executes SQL task 2

Pacakge execution with logical OR constraint

Conclusion

SSIS Precedence constraints are a useful feature of the SSIS package for defining workflow and task execution sequences. We can use it for validation checks, error handling and build a complete solution as per our requirement.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views