Implementing Conditional Task Execution with SSIS

By: Kasim Wirama, MCDBA, MVP SQL Server

If you have ever developed DTS package, you must be familiar with Precedence Constraint (Success, Failure, Completion) move from one task to another task. Some enhancements for these Precedence Constraint is added with conditional that you can apply with expression such as @var1 > 5, that should be evaluated before move execution from one task to another task.

In SSIS, you can define execution condition not only by constraint (success or failure) but also by conditional expression (<, >, <= , >= , <>) and mixed (constraint and expression) with AND and OR operator.

Let say, you have 2 tasks at Control Flow Designer, first task is Data Flow Tasks, second task is Execute SQL Tasks, you want second task is executed when first tasks contains records and it is executed without error.

You can achieve this scenario by using RowCount Component that counts number of rows returned from previous tasks. This component requires a variable that saves value of number of rows.

You can try it out, by make new package. Make OLEDB connection at connection manager area. Specify OLEDB source as SQL Server , database AdventureWorks.

Next you create SSIS variables, name it Var1 with Int32 type. And drag Data Flow Tasks into Control Flow designer area, then double click to switch to Data Flow designer. Drag OLE DB source, specify connection manager from OLEDB connection that you have just created, and point to table AWBuildVersion.

Drag RowCount component, connect OLEDB Source’s green arrow to it, and then double click RowCount. At Component Properties tab, assign Var1 variable to VariableName property, close the window by clicking OK button.

Switch back to Control Flow designer, drag Execute SQL task from toolbox to below Data Flow tasks, and connect Data Flow tasks to Execute SQL task. Edit Execute SQL task, and assign ConnectionType property as OLEDB, Connection property as OLEDB connection, and SQLStatement as select ‘dummy’. Edit precedence constraint (green arrow between 2 tasks), and in Precedence Constraint Editor choose Expression and Constraint, fill out Expression entry with @Var1 > 1 (assume number of rows in AWBuildVersion table is 1, so the expected behaviour is Execute SQL Task has been never got executed because it doesn’t pass from conditional expression @Var1 > 1).

Close Precedence Constraint Editor by click OK button. And you execute package now, for first time second task doesn’t get executed because number of rows is 1

End package execution, edit the precedence constraint, this time you change Expression to @Var1 <=1, execute the package again. This time you see that second task gets executed. The usage of RowCount component comes into play for this scenario.

SSIS gives you more choice by providing Expression criteria, besides constraint (success, failure, completion) that must be fulfilled by next task before allowing the task gets executed and combination between constraint and expression.