Add Expressions to Precedence Constraints

  • Article

A precedence constraint can use an expression to define the constraint between two executables: the precedence executable and the constrained executable. The executables can be tasks or containers. The expression can be used alone or in combination with the execution result of the precedence executable. The execution result of an executable is either success or failure. When you configure the execution result of a precedence constraint, you can set the execution result to Success, Failure, or 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.

The expression must evaluate to True or False and it must be a valid Integration Services expression. The expression can use literals, system and custom variables, and the functions and operators that the SSIS expression grammar provides. For example, the expression @Count == SQRT(144) + 10 uses the variable Count, the SQRT function , and the equal (==) and add (+) operators. For more information, see Integration Services (SSIS) Expressions.

In the following illustration, task A and task B are linked by a precedence constraint that uses an execution result and an expression. The constraint value is set to Success and the expression is @X >== @Z. Task B, the constrained task, runs only if task A completes successfully and the value of variable X is greater than or equal to the value of variable Z.

Precedence constraint between two tasks

Executables can also be linked by using multiple precedence constraints that contain different expressions. For example, in the following illustration, tasks B and C are linked to task A by precedence constraints that use execution results and expressions. Both of the constraint values are set to Success. One precedence constraint includes the expression @X >== @Z, and the other precedence constraint includes the expression @X < @Z. Depending on the values of variable X and variable Z, either task C or task B runs.

Expressions on precedence constraints

You can add or modify an expression by using the Precedence Constraint Editor in SSIS Designer and the Properties window that SQL Server Data Tools (SSDT) provides. However, the Properties window does not provide verification of the expression syntax.

If a precedence constraint includes an expression, an icon appears on the design surface of the Control Flow tab, next to the precedence constraint, and the ToolTip on the icon displays the expression.

Combining Execution Values and Expressions

The following table describes the effects of combining an execution value constraint and an expression in a precedence constraint.

Evaluation operation

Constraint evaluates to

Expression evaluates to

Constrained executable runs

Constraint

True

N/A

True

Constraint

False

N/A

False

Expression

N/A

True

True

Expression

N/A

False

False

Constraint and Expression

True

True

True

Constraint and Expression

True

False

False

Constraint and Expression

False

True

False

Constraint and Expression

False

False

False

Constraint or Expression

True

True

True

Constraint or Expression

True

False

True

Constraint or Expression

False

True

True

Constraint or Expression

False

False

False

To add an expression to a precedence constraint

External Resources

Technical article, SSIS Expression Examples, on social.technet.microsoft.com

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

Multiple Precedence Constraints

Precedence Constraints