Did you ever played with the control flow precedence constraints in SSIS; there are so many combinations that could put you in troubles if you don’t have a good understanding of them…
A connector (a flow constraint between 2 tasks basically) can result in one of this 3 states:
Success, Failure and Completion.
Success requires that the precedence executable succeed, Failure requires that the precedence executable fail, and Completion indicates that the constrained executable should run regardless of whether the precedence task succeeds or fails. For more information, see Precedence Constraints.
To complicate (actually to give you more flexibility) you can use Expressions too, to define the state of the connectors
@NumberOfEmployee > 0 ==> EXECUTE TASK process invoices for the employees
we want to exec a task called process invoices for the employees only if the expression@NumberOfEmployee > 0 where the var @NumberOfEmployee could be evaluated in another task…
This gives the possibility to define dynamically the task flow in you package, so much for powerful than a static flow, if you think about it.
To give you more freedom, you can combine static Constraint with Expression using logical AND and OR.
The previous example makes more sense if the @NumberOfEmployee has been evaluated or the data of the employees is valid, so we could do an AND with a hypothetical task Load Employee Data, if this Fails it doesn’t make sense to proceed so a
Success Load Employee Data AND @NumberOfEmployee > 0 ==> EXECUTE TASK process invoices for the employees
would be more robust in this scenario.
The official MSND define the True table of all this combinations between Constraint and Expressions in this way:
What I did is just create a package with some combinations of them and see what happens.
Let’s have a look…
It’s quite easy to understand .. I wtote down some combinations of Constraint and Expressions … the central is a script task that will just ends up in Success or Failure, so you can run the package and see it in action…
Now read again the The official MSND define the True table of all this possibility in this way …
The SSIS pkg: http://goo.gl/AtfMa
You can consider now and expert of Boolean Algebra…