Tuesday, January 3, 2012

SSIS Conditional Flow Constraints. (How to do an IF-THEN-ELSE flow like a normal flowchart)

This explains how to achieve a standard IF-THEN-ELSE style flow control so that it behaves the same way as you would expect if you had drawn a normal flowchart.  I just found this out so I wanted to share it as soon as possible so the explanation is very much straight to the point, so I hope it is still useful.

Here is another blog that I found after writing my own that describes the same thing. 


The aim:  We have a simple flow where we want to run A and C always, but if a certain condition (i.e. User:Id == 1) is true then we want to run B before C.  This "apparently" simple sequence is shown below.  When we run the flow below with User::Id == 1 then A runs, then B and then C, so all is fine so far.  However, when we run with User:Id == 0 then A runs but C will never run because it requires B to run with success as well.


The problem: With the default connectors, the condition for C to run is a logical AND of the constraints/conditions of its two input connectors. When you edit one of the connectors connecting to C you will see in the Multiple Constraints section that "Logical AND" is selected.  This is the problem that prevents C from running if B doesn't run as well.



The solution: Select the "Logical OR" option so that C will run if either of its input connectors conditions/expressions are true.  This will turn both the connectors joining to C into dotted lines as shown in the figure below.


Hope this helps! 



By the way. I found another solution here that uses Sequence Containers.