Container in SSIS – SQL Server Integration Services


Description Moving ahead from Import and Export Wizard, we will now discuss on the topic Containers in SSIS. In my previous post on SSIS and It’s Architecture, I discussed about Packages, Control and Data flow, Connection Managers, Containers, etc. Let’s recall some of the concepts and wrap it shortly:-

  1. Package: – A basic unit of Design, Deployment and Execution.
  2. Connection Managers: – A logical representation of connections stored in the package. These cannot be shared between the packages.
  3. Data Flow: – It is defined as the complete process of Extracting data into server’s memory, transforming it and Load the transformed data onto Destination.
  4. Control Flow: – It acts as the brain of a package. A package consists of a single control flow. It contains Containers, Tasks, Variables, Precedence constraints and Event handlers.

So, we are clear with the basics now. For further details, you can jump to my article on SSIS and its Architecture .  Now, let’s unite our concentration on the main point- Containers. They are classified as follows:-

  1. Task Host Containers: – This is an abstract concept like an interface. They are not visible in our toolbox and provides services to a single task.
  2. Sequence Container: It allows you to group tasks into logical subject areas. Within the development environment, you can then collapse or expand this container for usability.
  3. For Loop Container: – The basic function of this is to loop through a series of tasks contained in a container until a condition is met or for predetermined number of times.
  4. For Each Loop Container: It Loops through a series of files or records in a data set, and then execute the tasks in the container for each record in the collection.

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.

We will cover all the above mentioned Containers by implementing them separately with reference to simple examples. I am mentioning the links below, Click on the respective link who’s Implementation you want to see :-

Click Here for the   ==>  Implementation of SEQUENCE Container in SSIS.

Click Here for the   ==>  Implementation of FOR LOOP Container in SSIS.

Click Here for the   ==>  Implementation of FOREACH LOOP Container in SSIS.

With this we complete our post on Container in SSIS. I hope you liked it. Please provide your feedback as comments below.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (37 votes cast)
VN:F [1.9.22_1171]
Rating: +27 (from 27 votes)
Container in SSIS - SQL Server Integration Services, 9.2 out of 10 based on 37 ratings
  • Pingback: For Loop Container in SSIS | pHpRing()

  • r4

    Appreciate you sharing, great blog.Thanks Again. Cool.

  • J Shaun

    Great, thanks for sharing this blog.Thanks Again. Will read on…

  • venkat

    Hi bro plz let me know detailed abt script task in ssis

    • Hi Venkat,
      Please post it on PhpRinG’s Facebook page too, so that others can also provide views on this question. Bro, Script task requires little knowledge of VB and C#. I will surely provide you information on this soon. Stay tune !!! 🙂