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
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.
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:
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:
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:
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:
Let’s change it to failure and arrow colour changes to Red. It also shows an annotation for failures.
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:
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:
Another SSIS precedence constraint is completion. Sometimes, we want to execute another task even first task execution status is a success or fail:
In the following screenshot, executes SQL task 2 is successful despite task 1 failure:
It also executes task 2 if SQL task 1 is successful:
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
Double-click on precedence, and it opens the 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:
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
Click on Test, and it evaluates the expression:
Click OK, and it changes the expression symbol fx as shown below:
It runs the constrained executable if the evaluated expression is true:
If the expression returns false condition, it doesn’t execute the constrained executable:
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
Click OK, and it looks as per the following screenshot:
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
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
Similarly, we can change it to 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:
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
Multiple SSIS Precedence Constraints
Let’s make things more complicated. Add one more execute SQL task and connect it to SQL task 2:
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:
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:
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:
Click OK, and you can note the changes in SSIS precedence constraints lines. It changes lines from solid to 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
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023