Home > SSIS > Control the package execution flow – Precedence Constraint – SSIS

Control the package execution flow – Precedence Constraint – SSIS


Package execution flow is constructed by linking executable tasks by providing links between them. This link is called precedence constraints. This link is only available in control flow and Event handlers and you cannot see this in data flow. because, data flow task as data flow pipelines, we can view the data flowing from one task to another using data viewer and we have other tasks that takes care of the data flow direction.

Precedence constraint is used to direct the package execution flow. there are two options available to control the flow of package execution. They are Constraint and Expression.

Constraint are predefined conditional check in the package that we can use to link between control flow tasks.

Expressions are used to evaluate the user expressions to control flow of package execution.

we can also use these two options with different combinations of logical condition such as AND and OR as given in the section “Multiple constraint”

Precedence Constraint editor

To get this editor, we have Double click the connected link between tasks or Right click on the link and select edit option.

pic1This editor contains various blocks that we can use to setup the constraint.

Evaluation options:

pic2There are 4 evaluation options available here.

1. Constraint = it is built-in package execution condition

2. Expression = it is for user defined logical expression to evaluation the execution flow

3. Expression and Constraint = it is a combination of both with logical condition operator

4. Expression or Constraint = it is used to evaluate either constraint or expression

Evaluation Constraint values:

pic3

Success = only if task execution succeeded

Failure = only if task execution failed

Completion = Any one, success or failure

Expression text-box

It is used to provide the evaluation expression. We can use the package variables to create logical expressions.

Example:

@val1 > 1 && @val1 == 4

pic4

Test button is used to evaluate the conditional expression in design mode.

Multiple constraint

This option evaluates multiple constraints flowing to a task with logical condition AND and OR.

AND = All constraint to the task must be true

OR = Any one of the constraint should be true

Implementation:

I have a package with two tasks

1. Sequence Container
   it is an empty Sequence container.
2. Script Task.
     it has a message box to show some text during execution.
 

pic5

Precedence constraint is defined in the execution flow as shown below.

pic6

Executing the package with the evaluation condition year(getdate()) ==  2013

pic7

Executing the package with the evaluation condition year(getdate()) ==  2012

pic8

Conclusion

Precedence constraints are useful in many way for the developer to design a condition based task execution in their packages.

Advertisements
  1. Gurinder
    January 7, 2013 at 7:29 am

    Hi Ayyappan,

    You said in first paragraph “This link is only available in data flow and Event handlers.”
    Is it correct? I see Precedence Constraint is not available in DF, its available in CF.
    Thanks

    • January 7, 2013 at 11:07 am

      Thanks for your comment. You are correct. I totally missed that point.

  2. January 24, 2015 at 9:29 am

    THanks Sir..it is very good.

  1. No trackbacks yet.

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

%d bloggers like this: