Things you might not know about Precedence Constraints : SSIS Nugget

jamiet — Sat, 10 Oct 2009 22:11:06 GMT

Precedence constraints are one of the most oft used features of SQL Server Integration Services (SSIS) but also often one of the most overlooked. In this blog post I’ll cover a couple of things that you might not know about them.

The basics

Most people typically use precedence constraints like this, with a green arrow:

A green arrow here means “If ‘SEQ 1’ succeeds, execute ‘SEQ 2’”; that’s called on OnSuccess constraint. Like the DTS of old there are also OnError (Red) and OnCompletion (Blue) constraints which behave exactly as you would expect.

In SSIS you can also make precedence constraints conditional, so you could have an expression that states “Execute ‘SEQ 2’ IF ‘SEQ 1” is successful AND some condition is true as well”.

I’m sure most of you knew all that already, so here are a few things that you might not know.

Annotated Precedence Constraints

Firstly, did you know that precedence constraints can be annotated with the expression that is placed upon that precedence constraint? Each precedence constraint has a property called ‘ShowAnnotation’ and it has 5 possible values as depicted here:

Most of those options aren’t very useful but one of them, ‘Constraint Options’, can be very useful indeed if you have an expression on your constraint because it causes the expression to be displayed on your design surface like so:

Pretty neat! I am very much a fan of self-documenting packages and this is clearly a nod in that direction. I wish this were the default setting for this property but unfortunately it is not.

Precedence Constraint Scope

SSIS’s container hierarchy gives rise to the notion of scope. We are used to applying scope to a variable but its worth remembering that precedence constraints have scope as well. In the same way that every container* has a collection of variables, every sequence container, for loop container, for each loop container, eventhandler container and package container also has a collection of precedence constraints as is illustrated in this screenshot from MSDN:

Sequence Members (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence_members.aspx)

*Remember, every task, eventhandler and package is also a container

Why is this important? Well, it means that every variable referenced in a precedence constraint must be scoped to the container of which the precedence constraint is a member (that’s a bit of a mouthful but read it bit by bit and hopefully it will make sense).

This is worth remembering because there is a bug in the SSIS Package Designer in BIDS which manifests itself as you not getting a design-time error if a variable in your precedence constraint expression is scoped to the container that the precedence constraint is coming from. Phew another mouthful, let me explain by referring to a picture.

In this example if the variable @[User::NumberOfRows] were scoped to task “SQL Prepare some data” you would not get a design-time error but you definitely would get an error when you executed the package. The bug is at design-time, not execution-time.

Wrap-up

Ok, there’s nothing here that is particularly earth-shattering but I think it helps to know this stuff. If you have any comments or questions please post them below.

@JamieT

Search results matching tags 'SSIS', 'nugget', and 'sql server'

Things you might not know about Precedence Constraints : SSIS Nugget

The basics

Annotated Precedence Constraints

Precedence Constraint Scope

Wrap-up