What are containers and how does a container work in SSIS?

Problem: What are containers and how does a container work in SSIS?

Solution:    Integration Services Containers

Containers:

Container is an object of SQL Server Integration Services which help in formatting the tasks of a package in a boxed structure and they are also used to repeated executions called as for loop containers. Also Container can include another container

Use cases of containers in development of packages are as follows:

·         Looping for files in different directories based on enumeration.

·         Running list of stored procedures in sequence or logically expression.

·         Can be used in sending a different e-mail message n times, one time for every day of the week.

·         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.

Below is the list of Containers and the

Container	Description
Foreach Loop Container	Runs a control flow repeatedly by using an enumerator.
For Loop Container	Runs a control flow repeatedly by testing a condition.
Sequence Container	Sets the order of tasks inside a package.
Task Host Container	Provides services to a single task.

In the designer, you can see from toolbox the following containers:

Example:

In the example, I would like to show we can use for loop container to execute a sql statement for n number of times.

Step 0: Create a variable I and use the data type integer.

Step 1: From the tool box drag a for loop container and set the initial value for the variable and conditions to evaluate and how to increment in each iteration.

Step 2: Drag and drop an execute sql task inside the container and use a statement or procedure that you wish to execute for n number of times.

SQL Statement used:

  INSERT INTO dbo.TableA([Col1]

      ,[Col2]

      ,[Col3]

      ,[col4])

  SELECT 'Zone'+cast(max(RIGHT(col1,1))+1 as varchar(2)),max(col2)+1,max(col3)+1,'for loop' from dbo.tableA

After setting up step 1 and step 2 you should have a container like below:

Step 3: Execute the container or task.

Output:

Col1	Col2	Col3	col4
Zone5	4	1445	test
Zone6	5	1446	for loop
Zone7	6	1447	for loop
Zone8	7	1448	for loop
Zone9	8	1449	for loop
Zone10	9	1450	for loop
Zone10	10	1451	for loop
Zone10	11	1452	for loop
Zone10	12	1453	for loop
Zone10	13	1454	for loop
Zone10	14	1455	for loop
Zone10	15	1456	for loop

As you can see the tables has been populated ten times with the max values of the last run.