Unexpected SSIS Precedence Constraints Behaviour

In one of my projects, I had two simultaneous sequence containers. One is executed always, and one only rarely. So I decided to control the Task Flow with a package parameter. As soon as there was task I needed to do once all paths were completed, I ran into SSIS behaviour that was (to me) unexpected.

So first my attempt to have one task execute always, and one task execute only when the parameter 'PerformFullLoad' is set. The two tasks are preceded by a single logon to the source system. The source system then hands out a token that can be used in all communications.

ConditionalExecution

After both tasks are completed however, I need to do a proper logout too, as well as start the ETL process of the Data Mart.

My initial attempt was to just add those two tasks, and use precedence constraints with the 'Constraint OR Expression' option - because 'SEQ Load earlier years' would either execute (and need to finish) before logout, or the parameter 'PerformFullLoad' must be set:

PrecedenceConstraintsForlogout

As you can see, in this current setup an 'or' setting for the multiple precedence constraints is not an option: when the parameter 'PerformFullLoad' is True, both tasks need to complete before continuing. But when the parameter is False, only the 'Load Current Tables' task needs to be executed.

It turns out SSIS doesn't work that way - before a task with precedence constraints starts, all preceding tasks need to have finished (unless you set the multiple precedence constraints to 'or', meaning only one of them needs to have finished).

Package Execution Finished

To be sure I didn't miss something, I dropped my question at StackOverflow (http://stackoverflow.com/questions/35017005/how-to-use-ssis-precedence-expression-or-constraint-for-non-executing-items/35017892#35017892) and reached out to Andy Leonard if he could provide some more insight, which he did (Thanks, Andy!)

Turns out Andy has done a quite extensive review of Constraints already over at SQL Server Central: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/92901/. Shame on me I hadn't read that yet.

In the end, the solution I needed was to really 'split' the logic, such that whenever the left task is executing, the right is not - and vice versa. Which meant I needed to include the 'incremental' logic in the 'earlier years' container too:

Solution Package Problem

The solution. I contained the tasks of 'Load Current Tables' inside a separate package, making it easy to call the same logic on multiple places

It looks better, too 🙂 - less expressions, making the package even easier to read.

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleSSRS non-ugly #5: The nitty griddy details