Using Expressions On Precedence Constraints in Integration Services

By:   |   Comments (1)   |   Related: More > Integration Services Control Flow Transformations


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?


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, August 14, 2019 - 8:09:41 PM - freddy Back To Top (82065)

Thanks mate. I was looking for some useful information to understand the difference between constraints and expression. So now, I can continuing working in my ETL.

get free sql tips
agree to terms