Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Expressions On Precedence Constraints in Integration Services


By:   |   Read Comments   |   Related Tips: More > Integration Services Control Flow Transformations

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I have an Integration Services (SSIS) package with a couple of tasks in the control flow. There’s one task that I only want to execute on a certain condition, e.g. it should only execute on Saturdays. How can I do this?

Solution

In the SSIS control flow, you can control the execution of tasks using precedence constraints. These are the arrows going from one task/container to other tasks/containers.

SQL Server Integration Services precedence constraints

In the screenshot above, you can see two precedence constraints; one for failure and one for success. If the first Execute SQL Tasks succeeds, the data flow will be executed. However, if it fails, the other Execute SQL Task will run. There’s one more type: Completion. This means the other task will start once the first task has finished. It doesn’t matter if it was successful or not. You can find a good overview of precedence constraints and how they work in the tip Defining Workflow in SSIS using Precedence Constraints.

You can also put expressions on these precedence constraints. Let’s find out how we do this.

Using Expressions on Precedence Constraints in SSIS

Let’s assume we only want to run the data flow on Saturdays. The first step we need to do is edit the precedence constraint. You can open the editor by double-clicking the arrow.

SQL Server Integration Services precedence constraint editor

In the editor, you can see there’s a dropdown box for “evaluation operation”. There you have the following options:

  • -Constraint. This is the default and means either “success”, “failure” or “completed”.
  • -Expression. If you choose this option you need to specify an expression that evaluates to a Boolean value, i.e. -true or -false. The precedence constraint will go to the following task if the result of the expression is -true. Any constraint (success or failure) will be ignored, but of course the next task will only start if the current task has been completed.
  • -Expression and Constraint. In this case, the precedence constraint will evaluate the Boolean expression, but also a constraint. For example, the next task may only start if the current task fails and if the expression was met.
  • -Expression or Constraint. The same as the previous, but now only one of the two needs to evaluate to -true. For example, execute the next task if today is Friday or if the current task fails.
SQL Server Integration Services constraint options

In this example, we’re going to use Expression only. When you select this option, the value dropdown will be grayed out and you can now enter a value for the expression.

enter an expression in the SSIS Expression Editor

Click on the ellipsis to open the expression editor. We can construct an expression that checks if the current date is a Saturday by using the functions DATEPART and GETDATE:

DATEPART("weekday",GETDATE()) == 7
   

Keep in mind that depending on your locale settings, SSIS may treat Sunday as day 1 of the week, so Saturday is day 7.

Note: I’ve seen on some forums that people use Monday as day 1 instead. It’s dependent on local settings, but there’s almost no documentation on this. My local settings are configured to have Monday as the first day of the week, but still Sunday was considered the first day of the week in SSIS. Once I changed the localeid from the package from English (United States) to Dutch (Belgium), Monday was considered the first day of the week. However, this was only when the package was running! During design time, SSIS still considered Sunday to be the first day of the week. So, you potentially have an expression evaluating to true when testing the expression in the editor, but evaluating to false when actually running the package. Test thoroughly!

You can test the expression by clicking “evaluate expression”.

evaluate expression in SQL Server Integration Services

The expression returns false, so today is not a Saturday. When you click OK, you can also test the expression in the precedence constraint editor. This will however only check if your expression has valid syntax.

test syntax in SQL Server Integration Services

When you close the editor, you can see there’s an expression by the fx symbol.

fx symbol for expression in SQL Server Integration Services

If you want it to be clearer about what your precedence constraints are evaluating against, you can change the property ShowAnnotation to ConstraintOptions.

showannotation property in SQL Server Integration Services

Doing so will include all of the options alongside the constraint, so it is easy to see which logic is used in your package.

constraint options enabled in SQL Server Integration Services

It’s time to test our expression. The following screenshot is taken when the package is run on a Wednesday:

SSIS package is run on a Wednesday

You can see that only the first task has run, but the other two haven’t. When we change the expression to check for Wednesdays and run the package again, we can see the data flow task has run:

SSIS expression success when run on Wednesday
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

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 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools