22 Mar

SSIS: Precedent Constraints

Precedent constraints link tasks in the Control flow and control the behavior of downstream tasks.  They have two parts, constraint options and multiple constraints.  In a sense, the first part defines the test for an individual constraint while the section part defines the condition that all the constraints must meet to pass processing downstream to the next Task.

Hopefully, this will make more sense after I explain the transform.

Constraint options

These options apply to each path.  There are 4 evaluation operations.

  • Constraint: Evaluated based on the processing of the earlier task.  There are 3 possible conditions: Success, Failure and Completion.
  • Expression: Configured in the expression section.  This is the same expression tool you see everywhere else in SSIS.  The result of this is either True, the condition matches, or False, it is not matched.
  • Constraint and expression:  Both the constraint and the expression must be true.
  • Constraint or expression: Either the constraint is true, or, the expression is true, or, both.

The value field is essentially the result of the operation.  There are 3 potential outcomes.

  • Success: The earlier task executed successfully.
  • Failure: The earlier task failed.
  • Completion: The earlier task completed.

The second part of the editor applies when there is more than one constraint connected.  In that case one of two things can happen:

  • AND: All constraint conditions are True.
  • OR: At least one constraint condition evaluates to True.

This will make a lot more sense with some examples.  Hopefully this helps someone.  It’s a really basic programming concept but the way it’s implemented will probably confuse a few people.

Task1 and Task2 must both be successful.  They are so the next step runs.

Task1 must succeed, AND, Task2 must succeed.  They are both successful so the Next Step task runs.
Task1 or Task2 must be successful, and both are, so Next Step runs.

Task1 must succeed, OR, Task2 must succeed.  The are both successful so the Next Step task runs.

Task1 must be successful, and Task2 must complete.  Both meet the criteria and Next Step runs.

Task1 must succeed, AND, Task2 must complete. While Task2 fails, it does complete, so the Next Step task runs.

Task1 must fail, or Task2 mus fail, instead, both are successful.  The Next Step task does not run.

Task1 must succeed, OR, Task2 must succeed.  Both fail so the Next Step task is not run.

Task1 must succeed and Task 2 must complete.  Because Task1 fails, Next Step is not run, despite Task2 completing.

Task1 must succeed and Task 2 must complete. While Task2 runs (completes), Task1 fails, so the Next Step task is not run.

Task1 must succeed, or, Task2 must fail.  Instead, Task1 fails and Task2 succeeds.  The Next Step task is not run.

Task1 must succeed, OR, Task2 must fail. Instead, Task1 fails and Task2 succeeds, so the Next Step task is not run.

Task1 must succeed or Task2 must fail.  While Task1 fails, Task2 also fails which meets the condition so the Next Step task is run.

Task1 must succeed, OR,  Task2 must fail. Task1 and Task2 both fail, which satisfies the condition so the Next Step task runs.