SSIS Sequence Container

In this section, we'll introduce multiple precedence constraints first and then learn the first container - Sequence Container.

Let's see the example below.

  1. Create a new package named as "SequenceContainer.dtsx", drag and drop 4 script tasks, renamed and linked them as follows.
  2. SSIS Multiple Precedence Constraints

  3. Run the package and you'll see all the tasks run successfully. Then change the precedence constraint between task c and task D to Failure and run the package again.
  4. Multiple Precedence Result With One Failed Setting

    You can see the task D has never run because the precedence constraint between task c and task D return false and the multiple precedence was set as Logical AND by default.

  5. Click "Stop Debugging" or press shift F5. Right click one of the precedence constraints, choose "Edit..." to open Precedence Constraint Editor.
  6. SSIS Precedence Constraint Editor

  7. Set the multiple precedences as Logic OR and click "OK". Then all the lines were changed to dotted lines which stand for Logic OR among them.
  8. Multiple Precedence Constraints Logic OR

  9. Run the package again and you will find the task D runs great because of Logic OR. If one of the constraints linked to Task D returns true, the task D will run.

If the condition is changed to either both task A and task B returning success or task C returning failure, the task D will run. How can we realize this condition? That means if the current condition were A || B || C, the changed condition would be ( A && B ) || C. The solution is to add a sequence container.

A container is a subset of the SSIS package in the control flow and it can contain zero or more tasks and containers. A sequence container likes a sub package and you can add, edit or remove tasks as you do in the package. It can be configured through its property window. The benefits of using a sequence container are summarized below.

  • One location Management: Change a property of a sequence container instead of changing the same property of all the tasks in the container.
  • Variable Scope: Variables defined in the container scope are only accessed by the tasks or containers in the sequence container.
  • Collapsing and Expanding: Have this ability to group related tasks in a container to be managed easily.
  • Transaction: Easily to make the related tasks be finished in one transaction.

Now it is time to add a sequence container to the package above. Please follow the steps below.

  1. Open the package SequenceContainer.dtsx if it is closed. Then drag and drop a sequence container to the control flow of the package.

  2. Right click the precedence constraint between task A and D, then choose "Delete" to delete the constraint.
  3. Delete a Precedence Constraint

  4. Repeat the step 2 again to delete the constraint between task B and D too.

  5. Drag and drop the task A and B to the sequence container and link the precedence constraint of the container to the task D.
  6. Add a Sequence Container to Hold 2 Tasks

  7. Run the package and you will see the task D will run OK because both task A and B running successfully makes the whole container return success. At last, the logic OR makes the task D execute.

  8. Click "Stop Debugging".Then change the code of task B like we did before to make it return failure instead of success.

  9. Run the package again.
  10. Sequence Container Returns Failure with Logic OR

    Task B failed running makes the whole sequence container return failure so the task D has never run.

  11. Click "Stop Debugging".Then change the constraint between task C and D to Success and logic AND. Run the package again.
  12. Sequence Container Returns Failure with Logic AND

    The task D still cannot run because of the failure the container returns.

  13. Click "Stop Debugging".Then right click the sequence container and choose "Disable" to disable the container.
  14. Disable the Sequence Container

  15. Run the package and you will see the task D runs but the sequence container has never run because it was disabled. Click "Stop Debugging".

  16. Right the container and choose "Enable" to enable it. Then click upper arrow at the top right conner of the container to Collapse it.

  17. Run the package.
  18. Sequence Container Collapse and Run

    You can click the arrow to toggle collapsing and expanding the sequence container.

  19. Click "Stop Debugging" to terminate running the package.