By: Koen Verbeeck | Comments (1) | Related: More > Integration Services Control Flow Transformations
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.
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.
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.
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.
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”.
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.
When you close the editor, you can see there’s an expression by the fx symbol.
If you want it to be clearer about what your precedence constraints are evaluating against, you can change the property ShowAnnotation to ConstraintOptions.
Doing so will include all of the options alongside the constraint, so it is easy to see which logic is used in your package.
It’s time to test our expression. The following screenshot is taken when the 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:
Next Steps
- If you want to learn more about precedence constraints, check out the tip Defining Workflow in SSIS using Precedence Constraints.
- The tip SSIS Variable and Expression Example - Part 1 gives more examples on how you can use expressions to make SSIS more dynamic.
- Check out this overview for more SQL Server Integration Services Development Tips.
About the author
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 MSSQLTips.com. 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
