Tuesday, June 22, 2010

Sequence Container in SSIS

The Sequence Container defines a control flow that is a subset of the control flow in a package. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
There are some benefits of using a Sequence container which are mentioned below:
  • Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.
  • Managing multiple tasks in one location by setting properties on a Sequence Container instead of setting properties on the individual tasks.
  • Provides scope for variables that a group of related tasks and containers use.
For more information about using Sequence Containers, see Different ways of using Sequence Containers
Using Sequence Containers lets you handle the control flow in more detail, without having to manage individual tasks and containers. For example, you can set the Disable property of the Sequence container to True to disable all the tasks and containers in the Sequence container.

If a package has many tasks then it is easier to group the tasks in Sequence Containers and you can collapse and expand Sequence Containers.
Note: You can also create task groups which collapse and expand using the Group boxthis is a design-time feature that has no properties or run-time behavior.

Now I'll explain you how to use sequence Container in a package.
1. To begin, right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with SequenceContainer.dtsx as shown below:













2. Add a package variable DayOfWeek









3. Drag and drop Script Task from toolbox. This task will be used to initialize DayOfWeek variable with current day of week. Rename Task name as Script Task - Set DayOfWeek.
4. Double click on Script Task to open Script Task Editor. Enter User::DayOfWeek in ReadOnlyVariables property.
5. Click on Edit Script... and write below code in Main() function of ScriptMain.cs:

I have just attached an image of code to avoid formatting problems of the code.

6. Drag and Drop 7 Sequence Container tasks from Toolbox and rename the task on Week Days e.g. "SC - Sunday", "SC - Monday" etc.
I am not placing any controls flow items inside Sequence Container to narrow down the example and focus more on how to use sequence container.

7. Connect all these Sequence Containers to Script Task. Now double click on green arrow to open Precedence Constraint Editor. Select Evaluation operator as Expression and Constraint and Expression as @DayOfWeek == "Sunday". Click OK to close and save changes. Tthe Expression for remaining task will be differ as per week day e.g. @DayOfWeek == "Monday" for Monday and so on.


















8. Save the package. Now right click on the package in Solution Explorer and execute the package.
















9. This package will execute only one sequence container as per expression set for the precedence constraints.

















This is just an example of how we can use Sequence Container. But we can use this in many ways which depends on the requirement.

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. sir i tried this example but after executing my package. it appeared to me all sequence container gone GREEN.
    rather than it should be that only the who matches today's date should have gone green.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. this is a correct code:
      public void Main()
      {
      try
      {
      String dayOfWeek = String.Format("{0:dddd}", DateTime.Now);
      Dts.Variables["User::DayOfWeek"].Value = dayOfWeek;

      MessageBox.Show("Day Of Week = " + dayOfWeek);
      Dts.TaskResult = (int)ScriptResults.Success;
      }
      catch (Exception e)
      {
      Dts.Log(e.Message, 0, null);
      }

      }

      Delete
  3. when copy&paste, ("{0:dddd}", its a zero, not O

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi!

    Another Tip that we need to have in mind is: When you're editing the "Script Task Editor", (Before to edit the code in the new windows...) we must select the option "ReadWriteVariables" and selected the variable "User::DayOfWeek"... In the code, only you need select the variable name...

    http://123-juan-k.blogspot.com.co/2016/09/blog-post.html

    public void Main()
    {
    try
    {
    String dayOfWeek = DateTime.Now.DayOfWeek.ToString().ToUpper(); //In my case the day name is Upper
    Dts.Variables["DayOfWeek"].Value = dayOfWeek;

    MessageBox.Show("Day Of Week = " + dayOfWeek);
    Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {
    Dts.Log(e.Message, 0, null);
    }
    }


    ReplyDelete

Here are few FREE resources you may find helpful.