IS Control flow precedence constraints visually…

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

Ex

@NumberOfEmployee > 0 ==> EXECUTE TASK process invoices for the employees

means

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.

Ex

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:

image

 

Easy Ninja

What I did is just create a package with some combinations of them and see what happens.

Let’s have a look…

image

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…

Success scenario

ExpressConstrSucc

Failure scenario

ExpressConstrFail

Now read again the The official MSND define the True table of all this possibility in this wayThumbs up

Link

The SSIS pkg: http://goo.gl/AtfMa

ps

You can consider now and expert of Boolean Algebra…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s