SSIS tutorial: Precedence Constraints using Zappysys tasks

SSIS Tutorials
, , ,
1

Introduction

Precedence constraints in SSIS are the connectors (arrows) between tasks in the Control Flow. They determine the execution order and conditions under which tasks run. By default, tasks are connected with a success constraint, meaning the subsequent task executes only if the preceding task succeeds. However, you can customize this behavior using various evaluation operations to handle different scenarios, such as failures or conditional logic. In this article, we will teach you how to work with precedence constraints using constraints (success, failure, completion) and expressions.

Evaluation Operations

When configuring a precedence constraint, you can set the evaluation operation to define how the constraint is evaluated:

  • Constraint: Evaluates based on the task’s execution result (Success, Failure, or Completion).
  • Expression: Evaluates a Boolean expression; the subsequent task runs if the expression returns true.
  • Expression and Constraint: Both the task’s execution result and the expression must evaluate to true for the subsequent task to run.
  • Expression or Constraint: Either the task’s execution result or the expression must evaluate to true for the subsequent task to run.

Here is an example of the Precedence Constraint Editor:

Precedence Constraint Editor
Precedence Constraint Editor610×422 11.4 KB

Examples

Example 1: Constant Success and Failure

  1. Drag the REST API Web Task from the SSIS Toolbox into the Control Flow.

  2. Double-click the task to configure it.

  3. Set the HTTP Method to GET and provide the appropriate URL for your API endpoint.

  4. Configure any necessary headers or authentication details.

  5. Click OK to save the configuration.

  6. Drag two additional tasks, such as Logging tasks, into the Control Flow to handle Success and Failure outcomes.

  7. Right-click the arrow connecting the REST API task to the first Logging task and select Edit.

  8. In the Precedence Constraint Editor, set the evaluation operation to Constraint and the value to Success for the first logging task.

    Success option
    Success option610×422 11.1 KB

  9. Repeat for the second logging task, setting the evaluation operation to Constraint and the value to Failure.

    Failure option
    Failure option610×422 11.8 KB

  10. Click OK to apply the changes.

  11. Run the package and verify both scenarios.

    Final Result
    Final Result583×217 14.3 KB

Example 2: Expressions and Constraints

  1. Drag an Advanced File System Task into the Control Flow to handle file operations.

  2. Configure the task to count the number of files in a folder and store the result in a variable.

    Count files configuration
    Count files configuration588×466 23.9 KB

  3. Drag two additional Advanced File System Tasks: one to delete files if there are more than 10, and another to save the file list if there are 10 or fewer.

  4. Right-click the arrow connecting the counting task to the delete task and select Edit.

  5. In the Precedence Constraint Editor, set the evaluation operation to Expression and Constraint, set the value to Success, and enter the expression: @[User::Amount] > 10

    Expression and Constraint
    Expression and Constraint610×422 16.2 KB

  6. For the other branch, set the evaluation operation to Expression or Constraint, set the value to Failure, and enter the expression: @[User::Amount] <= 10

    Expression or Constraint
    Expression or Constraint610×422 14.9 KB

  7. Click OK to finalize the configuration.

  8. Run the package to verify behavior:

Conclusion

Precedence constraints are essential for building flexible and reliable SSIS packages. By combining expressions, success/failure logic, and ZappySys tasks, you can create dynamic ETL workflows that respond to a variety of conditions, ensuring data is processed accurately and efficiently. Using ZappySys tasks enhances SSIS packages by providing robust connectors and functions that simplify integration with external data sources and file systems.

Explore our SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.