Sequence Container in SSIS - Different ways of using Sequence Containers

Integration Services includes the Sequence container, which makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit.
Using Sequence containers lets you handle the control flow in a package in more detail, without having to manage individual tasks and containers.
If a package has many tasks, it can be helpful to group the tasks in Sequence containers.

I'll explain different scenarios where Sequence Container paly an important role to implement business rules.

Scenario 1:

• Sequence Task SC1 must execute first. SC1 may have many task with or without precedence constraints. The group of these task must execute prior to any other tasks in the package.
• Sequence Task SC2 must start after completion of SC1. SC2 must execute irrespective of Failure/Success of SC1.
• Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by SC2. SC3 must execute only after successfully execution of SC2. 

Scenario 2:

• Sequence Task SC1 must execute first.
• Sequence Task SC2 must start based on logical condition. This condition can be based on package variables. 
• Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by both SC1 and SC2. SC3 must execute after successfully execution of SC1. SC3 should execute regardless of SC2 but if SC2 executes, SC3 must execute after SC2.

  Scenario 3: This is real scenario in many BI applications.

• SC1 must execute prior to other task. This Sequence Container may contain many tasks to pull data from hetrogenious data sources.
• SC2 should be followed by SC1. This can be used to Insert/Update dimension tables and fact tables along with intermediate tables (if any). This container may further contain Sub - Sequence Containers e.g. SC2(a) to handle dimension Insert/Update and SC2(b) to update Intermediate and Fact tables.
• SC3 should be followed by SC2. This may contains SSAS Tasks to Build/Prcoess Cubes. This container may also have Sub - Sequence Containers e.g. SC3(a) to check whether Current Month partition exists or not. If Yes than Do Nothing Else Create Current Month Partitions using Script task. SC3(b) can be used to Process Dimensions and Current Month Partitions.
• SC4 can be used for Maintenence Plan tasks. If SC3 successfully completed (Evolution operation - Expression and Constraint) than take Backup of Cube, Shrink Datamart and Take Backup of Datamart.
• SC5 can be used for Recovery Paln. If SC3 fails, Restore Cube from previos & Latest Backup available..
  
  

However, there are many more ways of utilizing Sequence Containers. Further, things depends on complexity of business requirement rules defined.

Foreach Loop Enumerators

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

SSIS provides the following enumerator types:

• Foreach File Enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the folder and its subfolders.
• Foreach Item Enumerator to enumerate items that are collections. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
• Foreach ADO Enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.
• Foreach ADO.NET Schema Rowset Enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables of your SQL Server database.
• Foreach From Variable Enumerator to enumerate the object that a specified variable contains. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of Servers.
• Foreach Nodelist Enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the products of Electronics type: /products/product[@type='Electronics'].
• Foreach SMO Enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database.

Dynamic Database Connection using SSIS ForEach Loop Container

Did you ever come across a requirement where you have to run the same SQL statement(s) in multiple database server/instances using SSIS?

Many of my friends have asked about how to connect through multiple Databases from different Server using single Dynamic Connection. I want to explain this feature in this article. Basically, I want to execute one query (to calculate Record Counts for a given table) on a set of servers (which can be Dev, Test,UAT, PreProduction and Production servers). In my example, I am using ForEach Loop to connect to the servers one by one--> Execute the Query --> Fetch and store the data.

So here is the approach:

• Create a Table in your local database (whatever DB you want) and load all the connection strings. Within SSIS package, use Execute SQL Task to query all the connection strings and store the result-set in a variable of object type.
• Use ForEach Loop container to shred the content of the object variable and iterate through each of the connection strings.
• Place an Execute SQL task inside ForEach Loop container with the SQL statements you have to run in all the DB instances. You can use Script Task to modify your query as per your need.

Below is the details with an example:

STEP1:
To begin, Create two tables as shown below in on of the environment:

-- Table to store list of Sources
CREATE TABLE SourceList (
   ID [smallint],
   ServerName [varchar](128),
   DatabaseName [varchar](128),
   TableName [varchar](128),
   ConnString [nvarchar](255)
)

GO

-- Local Table to store Results
CREATE TABLE Results(
   TableName  [varchar](128),
   ConnString [nvarchar](255),
   RecordCount[int],
   ActionTime [datetime]
)
GO

STEP 2:
Insert all connection strings in SourceList table using below script:
INSERT INTO SourceList

SELECT 1 ID,
'(local)' ServerName, --Define required Server
'TestHN' DatabaseName,--Define DB Name
'TestTable' TableName,
'Data Source=(local);Initial Catalog=TestHN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;' ConnString
 
Insert as many connections as you want.
 
STEP 3:
Add new package in your project and rename it with ForEachLoopMultipleServers.dtsx. Add following variable:
 

Variable	Type	Value	Purpose
ConnString	String	Data Source=(local); Initial Catalog=TestHN; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False;	To store default connection string
Query	String	SELECT '' TableName, N'' ConnString, 0 RecordCount, GETDATE() ActionTime	Default SQL Query string. This can be modified at runtime based on other variables
SourceList	Object	System.Object	To store the list of connection strings
SourceTable	String	Any Table Name. It can be blank.	To store the table name of current connection string. This table will be queried at run time

STEP 4:
Create two connection managers as shown below:


Local.TestHN: For local database which has table SourceList. Also this will be used to store the result in Results table.
DynamicConnection: This connection will be used for setting up dynamic connection with multiple servers.
Now click on DynamicConnection in connection manager and click on ellipse to set up dynamic connection string. Map connection String with variable User::ConnString.

STEP 5:
Drag and drop Execute SQL Task and rename with "Execute SQL Task - Get List of Connection Strings". Now click on properties and set following values as shown in snapshot:
Result Set: Full Result Set
Connection: Local.TestHN
ConnectionType: Direct Input
SQL Statement: SELECT ConnString,TableName FROM SourceList

Now click on Result Set to store the result of SQL Task in variable User::SourceList.

STEP 6:
Drag and drop ForEach Loop container from toolbox and rename with "Foreach Loop Container - DB Tables". Double click on ForEach Loop container to open Foreach Loop Editor. Click on Collection  and select Foreach ADO Enumerator as Enumerator. In Enumerator configuration, select User::SourceList as ADO object source variable as shown below:

STEP 7: Drag and drop Script Task inside ForEach Loop container and double click on it to open Script Task Editor. Select User::ConnString,User::SourceTable as ReadOnlyVariables and User::Query as ReadWriteVariables. Now click on Edit Script button and write following code in Main function:

public void Main()

{
   try
   {
      String Table = Dts.Variables["User::SourceTable"].Value.ToString();
      String ConnString = Dts.Variables["User::ConnString"].Value.ToString();
      MessageBox.Show("SourceTable = " + Table + "\nCurrentConnString = " + ConnString);
      //SELECT '' TableName,N'' ConnString,0 RecordCount,GETDATE() ActionTime
      string SQL = "SELECT '" + Table + "' AS TableName, N'" + ConnString + "' AS ConnString, COUNT (*) AS RecordCount, GETDATE() AS ActionTime FROM " + Dts.Variables["User::SourceTable"].Value.ToString() + " (NOLOCK)";

      Dts.Variables["User::Query"].Value = SQL;
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception e)
   {
      Dts.Log(e.Message, 0, null);
   }
}
 
STEP 8:
Drag and drop Data Flow Task and double click on it to open Data Flow tab. Add OLE DB Source and Destination. Double click on OLE DB Source to configure the properties. Select DynamicConnection as OLE DB connection manager and SQL command from variable as Data access mode. Select variable name as User::Query. Now click on columns to genertae meta data.

Double click on OLE DB Destination to configure the properties. Select Local.TestHN as OLE DB connection manager and Table or view - fast load as Data access mode. Select [dbo].[Results] as Name of the table or the view. now click on Mappings to map the columns from source. Click OK and save changes.
Finally DFT will look like below snapshot:

STEP 9: We are done with package development and its time to test the package.
Right click on the package in Solution Explorer and select execute. The message box will display you the current connection string.

 Once you click OK, it will execute Data Flow Task and load the record count in Results table. This will be iterative process untill all the connection are done. Finally package will execute successfully.

You can check the data in results table:
Here is the result:

SELECT * FROM SourceList

SELECT * FROM Results

Foreach Loop Container in SSIS

The Foreach Loop Container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

I have already mentioned about Foreach Loop Enumerators in my previous article. Now I'll explain Foreach Loop Container using Foreach File enumerator. I'll move data of multiple excel sheets into a database table.

1. To begin, I have created three Excel Spreadsheets and store these sheets at location D:\SSIS\Hari\DataFiles.
These files have sample data for my example. Below is thesnapshot of file 2010-06-20.xlsx:









2. Open existing project or create new project using BIDS. I will use existing project Sample saved at location D:\SSIS\Hari\Sample (which I have used for other articles as well). Right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with ForeachLoopContainer.dtsx as shown below:













3. Drag and drop Foreach Loop Container from Toolbox. Add a package variable FileName of String type and assign default value D:\SSIS\Hari\DataFiles\2010-06-20.xlsx. I'll use this variable to store File Name of each iteration.

4. Drag and drop Data Flow Task inside Foreach Loop Container and rename it with DFT - Load Data From Excel.

5. Double click the Foreach Loop container to open Foreach Loop Editor to configure the Foreach Loop container. Click on Collection and provide the folder that contains the files to enumerate (in our example D:\SSIS\Hari\Sample), specify a filter for the file name and type *.xlsx and specify the fully qualified file name as retrieve file name as shown below:

6. Click on Variable Mappings and Select variable User::FileName from dropdown box. Corresponding Index for this variable will be 0. Click OK to proceed and save changes.

7. Add Excel Connection Manager to read data from Excel sheets:- Right click on Connection Managers area and select New Connection... and select EXCEL as type and click on Add button. Enter D:\SSIS\Hari\DataFiles\2010-06-20.xlsx in Excel File Path and make sure that checkbox First row has column names is checked. Click OK to proceed and save changes.

8. Config ConnectionString with FileName variable:- Click on Excel Connection Manager --> go to properties --> Click on Expression --> This will open Property Expression Editor. Select ExcelFilePath from Property dropdown box. Click on Expression and Drag and drop User::FileName from Variables. Click OK two times and save changes.

9. Add OLEDB connection manager to load data into database table: Right click on Connection Managers area and select New OLEDB Connection... This will open Configure OLE DB Connection Manager. Click on New button. Enter Server name and database name in respective boxes as shown below:

10. Double click on Data Flow Task to go to Data Flow tab. Drag and drop Excel Source from Data Flow Sources and double click on it to open Excel Source Editor. Select Excel Connection Manager in OLEDB connection manager and Sheet1$ in Name of the Excel sheet dropdown boxes.

11. Click on Columns in left side window. This will display all the External Columns available in excel and Output Columns. Click OK to process and save changes.

12. Drag and drop OLEDB Destination from Data Flow Destinations. Click on Excel Source to see green and red output lines. Connect green output line to OLEDB Destination. Now double click on OLEDB Destination to open OLE DB Destination Editor. Select local.Test as OLE DB connection manager. Click on New... button to create Destination table. It will open Create Table wizard. You can edit T-SQL statement as per your need. You can change the name of table if you wish. Click OK to proceed.

13. Click on Mappings to map source and destination columns. It will automatically map the columns by their matching names. However you can map the columns manually as well. Click OK to process and save changes.

14. We are done with package development. Finally package looks one as shown below:

Now its time to execute the package and check the result.

Go to package location D:\SSIS\Hari\Sample\SSIS-Sample1 --> Right Click on package ForeachLoopContainer.dtsx and click SQL Server 2008 Integration Services Package Execution Utility. This will open Package Execute Utility wizard.

Click on Execute button. A new wizard Package Execution Progress will open. You can see progress of package execution in this wizard.

Now you can check the data of all excel files into database table as shown below:

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.

For Loop Container in SSIS

The For Loop Containers are used for repeating control flow in SSIS packages. The Loop implementation is very similar to the For Loop structure in programming languages. In each iteration of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop Container uses the following elements to define the loop:

• An optional initialization expression that assigns values to the loop counters.
• An evaluation expression that contains the expression used to test whether the loop should stop or continue.
• An optional iteration expression that increments or decrements the loop counter.

The following example shows a For Loop container displaying Current Value of Expression through Message Box.
1. To Begin, Open SSIS Project or create new Project through BIDS. Add a new package and rename it with ForLoopContainer.
2. Add two package variables - StartValue and EndValue, both of integer type.

3. Drag and drop For Loop Container from toolbox and double click on it to open For Loop Editor. Set InitExpression, EvalExpression, and AssignExpression with @StartValue, @StartValue <= @EndValue and @StartValue = @StartValue + 1 respectively as shown below:

This loop repeats five times because StartValue is set to 1 and EndValue is set to 5.
4. Drag and drop Script Task inside For Loop Container and double click to open Script Task Editor.
Type User::StartValue in ReadOnlyVariables property of script Task editor. Now Click on Edit Script... button to write code

5. Write following code in Main() function to display a message box for current value of StartValue variable in loop iteration:
MessageBox.Show("StartValue = " + Dts.Variables["User::StartValue"].Value.ToString());

Main function will look like this:


6. Close script task editor and save changes. Now right click on Package in Solution Explorer and click Execute to execute the package.

The package will show Message box 5 times before completion.

Thats all. We can apply any logic inside For Loop Container as per business requirement.

SSIS Containers

Integration Services Containers
SSIS Containers are controls (objects) that provide structure to SSIS packages. Containers support repeating control flows in packages and they group tasks and containers into meaningful units of work. Containers can include other containers in addition to tasks.

Types of SSIS Container
SSIS provides four types of containers. I'll explain these containers with example in my following Posts.
The following table lists the container types:

Container Type	Container Description	Purpose of SSIS Container
Foreach Loop Container	This container runs a Control Flow repeatedly using an enumerator.	To repeat tasks for each element in a collection, for example retrieve files from a folder, running T-SQL statements that reside in multiple files, or running a command for multiple objects.
For Loop Container	This container runs a Control Flow repeatedly by checking  conditional expression (same as For Loop in programming language).	To repeat tasks until a specified expression evaluates to false. For example, a package can send a different e-mail message seven times, one time for every day of the week.
Sequence Container	Groups tasks as well as containers into Control Flows that are subsets of the package Control Flow.	This container group tasks and containers that must succeed or fail as a unit. For example, a package can group tasks that delete and add rows in a database table, and then commit or roll back all the tasks when one fails.
Task Host Container	Provides services to a single task.	The task Host container encapsulates a single task. But this task is not configured separately in SSIS Designer. It is configured when you set the properties of the task it encapsulates.

Containers are fundamental to the operation of transactions, checkpoints and event handlers. Each container has some common properties that affect the usage of these features. Understanding these properties and what they do helps a lot in the developing SSIS packages.

Property	Description
DelayValidation	A Boolean value that indicates whether validation of the container is delayed until run time
Disable	A Boolean value that indicates whether the container runs
DisableEventHandlers	A Boolean value that indicates whether the event handlers associated with the container run
FailPackageOnFailure	A Boolean value that specifies whether the package fails if an error occurs in the container.
FailParentOnError	A Boolean value that specifies whether the parent container fails if an error occurs in the container.
IsolationLevel	The isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.
MaximumErrorCount	The maximum number of errors that can occur before a container stops running.
TransactionOption	The transactional participation of the container. The values are NotSupported, Supported, Required.

reference: bol