By: Nat Sundar | Comments | Related: More > Integration Services Development
Problem
What is a precedence constraint and how can I use it to define workflow in SQL Server Integration Services (SSIS)?
Solution
Precedence constraints help us create a work flow in a SSIS package. With the help of precedence constraints you can connect tasks to control the flow. Based on the configuration, the precedence constraints can be represented as solid or dotted lines with green, red or blue color.
We need a precedence executable and a constrained executable to successfully create a precedence constraint. In the below image the "SQL Task" is referred to as a "precedence executable" and the file system task is referred to as a "constrained executable".
Configuration for Precedence Constraints in SSIS
The precedence constraint can be configured to work based on an SSIS expression or based on the precedence executable execution status.
A precedence executable (Task or Container) can have three execution statuses. They are Success, Failure and Completion.
The execution status "Success" indicates the successful execution of the precedence executable. This is usually indicated by green tick on the right top corner of the executable in SQL Server Data Tools (SSDT) designer. The below image indicates the successful execution of the SQL task.
The execution status "Failure" indicates that the precedence executable has failed to execute. This is usually indicated by red tick on the right top corner in SSDT designer. The below image indicates that the SQL task has failed to execute.
The execution status "Completion" indicates that the precedence executable has successfully completed or failed to execute.
Now let's take a simple example to understand the differences between these statuses. In the sample package, I have a SQL Task followed by a File System Task to copy a file. These two tasks have been connected with a precedence constraint. The SQL Task has been set to execute a simple SQL statement and store the output values in a variable as shown below.
The below image indicates the configuration of the File System Task.
Successful SSIS Package Execution
By default the File System Task will be executed after the successful completion of the SQL Task. This is represented by a solid green line between the two tasks. The below image represents the successful execution of both executables.
SSIS Package Failure Scenario
I have added a Send Mail Task and connected it with the SQL Task executable. Now I would like to configure it in such a way that the Send Mail Task will be executed if the SQL Task fails. If the SQL Task is successful, then the File System Task will get executed. If the SQL Task fails then it will execute the Send Mail Task. The failure case can be configured by changing the condition from successful to failure in the precedence constraint as shown below.
Now to make the SQL Task to fail, I have mistyped the table name as sys.tables1.
The below image represents the overview of the package with the Send Mail Task.
The below image represents the configuration changes for the failure case scenario.
The below image represents the execution status for the failure case.
SSIS Package Completion Scenario
Now let's say we want to FTP a file, after the completion (irrespective of success or failure) of the SQL task. This can be achieved by changing the condition to "Completion" as shown in the image below.
The below image represents the package after the configuration changes.
The below image confirms the FTP Task will be executed if the SQL Task has completed successfully.
The below image confirms the FTP Task will be executed if the SQL Task has failed.
This confirms the fact the completion status will allow the task to execute irrespective (Success or Failure) of the completion status.
Summary
Precedence constraints are one of the powerful features of SSIS. We can develop and support many validation checks using precedence constraints.
Next Steps
- Read more about Precedence Constraints here.
- Stay tuned to read about the advanced features of Precedence Constraints in a future tip.
About the author
Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
