SSIS Precedence Constraint

The executables in SSIS refer to tasks or containers. A precedence constraint links 2 executables: the precedence executable and constrained executable. See an example below.

SSIS Precedence Constraint Definition

The running logic of the above example can be shown below.

SSIS Precedence Constraint Running Sequence

The precedence executable runs first and then evaluate the precedence constraint result. If it returns true, then the constrained executable will be run otherwise the process will be over. The result of a precedence constraint depends on whether precedence executable executing result matches the precedence constraint setting, an expression result or the result of both. An executable can generate either Success or Failure result after it runs. A precedence constraint can be set one of the 3 results: Success, Failure and Completion. The following is the setting and result of a precedence constraint.

NameLook & FeelSettingResult
Green ArrowGreen Arrow SuccessReturns true only if precedence executable runs Success.
Red ArrowRed Arrow FailureReturns true only if precedence executable runs Failure.
Blue ArrowGreen Arrow CompletionAlways returns true no matter what result precedence executable runs.

Also the result of a precedence constraint can be evaluated by an expression you defined or both. See the example below.

  1. Create an new package in the project LearnSSIS1 and rename the package to PrecedenceContraint.dtsx.

  2. Open the package and define a variable V with Int32 type and 1 as its default value.

  3. Drag and drop a script task to the package and rename the task to "Source". Then Copy and paste the task and rename the copy to "Destination". At last using precedence constraint links them together as follows.
  4. SSIS Precedence Constraint Example


  5. Run the package you will get the following result.
  6. SSIS Precedence Constraint Example Result


  7. Click "Stop Debugging" and open the source or the destination script task you will find the default source code below.
  8. SSIS Script Task Default Code


  9. Change the code in line 95 in "Source" task to the following and click "OK" button to save it.

  10. Dts.TaskResult = (int)ScriptResults.Failure;
    

  11. Run the package again, you will get the result below. Then click "Stop Debugging".
  12. SSIS Script Task Returns Failure

    Because The "Source" task returns "Failure" but the precedence constraint was defined to continue running if the source returns "Success". In this case, the package stopped running after the "Source" task executed.


  13. Right click the green arrow and choose "Failure".
  14. Change Precedence Constraint to Failure


  15. Run the package again, you will see the "destination" task runs successfully because the "Source" returns Failure which meets the precedence constraint setting.
  16. Precedence Constraint is set to Failure and result


  17. Right click the red failure arrow and choose "Edit..." to open precedence constraint editor.
  18. SSIS Precedence Constraint Editor

    In Constraint options, you can choose one of the 4 evaluation operations.

    • Constraint
    • Expression
    • Expression and Constraint
    • Expression or Constraint

    The default one is Constraint which we already tried in the previous steps. The value is the constraint value we set and the current value is Failure. That means only if the "Source" task returns failure we'll run "Destination" task.


  19. Select "Expression and Constraint" and click "..." button in Expression to open Expression Builder to create an expression @[User::V] == 1 and click OK button.
  20. SSIS Expression and Constraint Example


  21. Leave "Multiple constraints" as the default "Logical AND" and click OK to save the setting. Then Run the package.
  22. SSIS Expression and Constraint Result

    You can see the "Destination" task ran OK because both constraint and the expression returns true. "fx" in the above picture means the precedence constraint contains expression condition.


  23. Right click the green arrow and choose "Failure".
  24. Change Precedence Constraint to Failure


  25. Click "Stop Debugging" and change the variable V's value to 2 in Variables window. Run the package again. You will see the "destination" task have not run because the expression @[User::V] == 1 returns false.

  26. Right click the precedence constraint and change the evaluation operation to "Expression or Constraint" and click OK.
  27. SSIS Expression or Constraint


  28. Run the package again and you will find the "Destination" task runs successfully because the Constraint and Expression are logic OR ( True || False = True ).

For Multiple constraints, we'll discuss it in the next section.