Unexpected SSIS Precedence Constraints Behaviour
27 januari 2016 2016-01-27 10:31Unexpected 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.
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:
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).
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!)
@kvstrien I attempted to answer. @jamiet may have another (and better) approach. Hope this helps :{> #SSIS
— AndyLeonard (@AndyLeonard) January 26, 2016
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:

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