THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Things you might not know about Precedence Constraints : SSIS Nugget

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:

image

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:

image

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:

precedence constraint showannotation options

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:

image

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.

image

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

Published Sunday, October 11, 2009 12:11 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dan English said:

Thanks for the tip.  I have always placed annotations with this information on the package.  This is very slick way to improve the self documentation capabilites for constraints and it should be the default setting.  I have always turned on the 'Show precendence constraint labels' in the designer options for SSIS, but this even better when you get the expressions.  Thanks again Jamie.

October 17, 2009 8:31 AM
 

Gary said:

Hi

I have an interesting SSIS problem that perhaps you are familiar with?

Assume a simple package with a SQL task that performs "Select 1" and stores the 1 to a package variable var_1.

Then have two constraints coming from this task, based on the evaluation of var_1.  In my example I have them going to two separate script tasks (with no content).

Then have the precedent constraints from these two tasks fall through to a common final task (again I used an empty script task).

So we have a task with two pathways that then rejoin.  If you run this, it will run "celan", but stop before the task that re-unites the two pathways.

This illustrates my issue, with several data tests requiring logging to SQL table, then a common task to read the table and email the results.  If only one pathway (I have tried all) exists, all works well.  If there are two pathways that have an expression evaluation on them upstream, the task with multiple entry paths seems to stop the process dead.

Ideas?

November 3, 2009 4:49 PM
 

jamiet said:

Hi Gary,

It sounds a little strange. Feel free to email me the package and I can take a look.

jamie[at]jamieDASHthomsonDOTnet

-J

November 5, 2009 1:30 AM
 

Rob said:

I have a similar problem to Gary. I have a script task that writes an XML response file in the event of a data validation error. It won't execute if it has more than one precedence constraint as input.

December 15, 2009 12:25 PM
 

Neelam Gupta said:

Nice Article

January 2, 2010 5:33 AM
 

Koen Verbeeck said:

Very nice article.

Today I encounterd the problem that you described regarding the scope of the variable referenced in the precedence constraint. I created the variable, not realizing that I accidentely had clicked on the SQL Task and bam, wrong scope without me noticing it.

So, as you described, no error at design time but an error at runtime. However, if I set the package to DelayValidation = True, the package would run without an error. How is this possible?

April 29, 2010 9:31 AM
 

Cam said:

I am trying to read all the precedence constraints in the package. I can iterate through all the executables just fine. How do you find the contstraint on a TaskHost?

If I have a container of type TaskHost, I don't see where I can get the constraints. The ForEachLoop, ForLoop and Sequence are no problem.

Thanks,

Cam

July 20, 2010 3:04 PM
 

Michael said:

Jamie, miss you at Conchango. Glad to see you found a new home. I have a strange one for you that I've googled all over to no avail.  I would like to have a package query an Oracle Audit Table that tracks if a overnight job completed. If there is an entry in JOBEND_DATETIME for the max(seqID) ONLY THEN do I want the package to do its magic. Otherwise, I would like to sleep for 5 min and recheck. Do this up to 9 times. If after 9 times, the field is still not populated, I want to end the package as FAILED and issue a SENDMAIL.  I cannot figure out how to poll without using a ForLoop which requires it to run 9x. I only want it to run UP TO 9x but do its magic at the Nth time when the field is filled in. Sequence Container was my next step. Any ideas? Sorry for the random question but I could not find another topic that was closely related!  Thanks!!!  mrp

September 15, 2010 3:03 PM
 

Arko said:

Hi, For some reason I don't have the property 'ShowAnnotation' at all, there is even no 'Design' category in the properties window. Has anyone else had the same issue?

Microsoft Visual Studio 2005

Version 8.0.50727.762  (SP.050727-7600)

Microsoft .NET Framework

Version 2.0.50727 SP2

Installed Edition: IDE Standard

SQL Server Integration Services  

Microsoft SQL Server Integration Services Designer

Version 9.00.1399.00

Regards,

Arko

March 28, 2011 11:18 AM
 

Steve W said:

Does anyone know fo a solution/workaround for the problem described by @Gary & @Rob? I have the exact same problem and can't find a solution. Thanks.

--SW

May 25, 2011 10:45 AM
 

Steve W said:

Problem Described above solved. Re-Config one of the Precedence Expressions to "Logical OR".

(http://www.sqldev.org/sql-server-integration-services/two-tasks-connected-to-one-task-gt-fails-8128.shtml)

May 25, 2011 10:59 AM
 

Jack Corbett said:

A little late to the party, but I definitely agree that the default for ShowAnnotation should be ConstraintOptions.  It's a pain to have to go make that change on every constaint.

August 4, 2011 10:53 AM
 

Steven Neumersky, CBIP said:

Jamie,

Within a precedence constraint expression can you use a property from another task?  For example:

\Package\Execute SQL Task.Properties[Disable] == True

OR, would I need to create and assign a variable on the above task property and THEN use the value of that VARIABLE in the precedence constraint?

For example:  

1. Variable @DisableSQLTask created as a boolean

2. Execute SQL Task.Disable is set to @DisableSQLTask

3. EvaluateAsExpression set to TRUE.

4. Set the precedence constraint expression to @DisableSQLTask == True

September 21, 2011 10:49 AM
 

jamiet said:

Hi Steven,

No, you can't reference proeprties of the package or properties of objects within the package (except for variables) I'm afraid. Your suggested approach to base both expressions on the same variable is exactly the right approach.

Regards

JT

September 21, 2011 11:34 AM
 

Emil Glownia said:

Hi Jamie,

I hope you don't mind if I share a link to a video that gives more examples? http://www.katieandemil.com/video-ssis-precedence-constraint-show-annotation-property-ssis-2012

in SSIS 2012 show annotation actually is not always updated (video 4min 10sec) and I raised a bug (a minor bug) on ms connect https://connect.microsoft.com/SQLServer/feedback/details/774622/ssis-2012-precedence-constraint-show-annotation-result-is-not-updated

Take care

Emil

December 18, 2012 5:15 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement