Container objects in SSIS

Container is a manageable unit of work. Containers are used to define a scope of variables, to implement transaction on multiple tasks and to create a group of tasks and containers for repeated execution. Each SSIS Package itself is a container at top level. In SSIS package we can add following three types of containers:

· Foreach Loop Container: Repeat tasks for each element in a collection, such as files in a folder, schemas. For example, a package can run Transact-SQL statements that reside in multiple files.
· For Loop container: 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 and containers into control flows that are subsets of the package control flow.
· Task Host Container: Provides service to a single task. Each task itself is also a container.

Let us first see Foreach loop container: Suppose we have many files in C:\ drive and we need to load all those files into a SQL Server table. The name and number of files is not static so we can not use file connections for each file. Here we will use Foreach Loop container as shown in below screenshot.



Let us talk about another scenario. We have to execute a group of task for 4 times. This can be implemented using For Loop container as shown in below screenshot:



Sequence container is used to group multiple tasks, define the limited scope of variables and to implement transaction. Even all types of container support transaction.