Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Defining Workflow in SSIS using Precedence Constraints


By:   |   Read Comments   |   Related Tips: 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".

SSIS Package Overview

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.

SSIS Package Successful Execution

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.

SSIS Package Failure Execution

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.

SSIS Package SQL Task Configuration

SSIS Package SQL Task Configuration Result set

The below image indicates the configuration of the File System Task.

SSIS Package File System Task Configuration

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.

Successful SSIS Package Execution

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.

SSIS Package SQL Task With Invalid SQL Expression

The below image represents the overview of the package with the Send Mail Task.

Send Mail Configuration Overview in SSIS Package

The below image represents the configuration changes for the failure case scenario.

Configuration For Failure in SSIS Package

Failure Configuration Changes Overview in SSIS Package

The below image represents the execution status for the failure case.

SSIS Package execution of Send mail task after SQL Task

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.

SSIS Package Completion Scenario

SSIS Package Completion Scenario Configuration

The below image represents the package after the configuration changes.

SSIS Package after the configuration changes

The below image confirms the FTP Task will be executed if the SQL Task has completed successfully.

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.

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.


Last Update:






About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with Bachelor Degree in Engineering.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools