Add Expressions to Precedence Constraints

 

Applies To: SQL Server 2016

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.

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

Evaluation operationConstraint evaluates toExpression evaluates toConstrained executable runs
ConstraintTrueN/ATrue
ConstraintFalseN/AFalse
ExpressionN/ATrueTrue
ExpressionN/AFalseFalse
Constraint and ExpressionTrueTrueTrue
Constraint and ExpressionTrueFalseFalse
Constraint and ExpressionFalseTrueFalse
Constraint and ExpressionFalseFalseFalse
Constraint or ExpressionTrueTrueTrue
Constraint or ExpressionTrueFalseTrue
Constraint or ExpressionFalseTrueTrue
Constraint or ExpressionFalseFalseFalse

To add an expression to a precedence constraint

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

Multiple Precedence Constraints
Precedence Constraints

Community Additions

ADD
Show: