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:-
- Package: – A basic unit of Design, Deployment and Execution.
- Connection Managers: – A logical representation of connections stored in the package. These cannot be shared between the packages.
- 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.
- 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:-
- 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.
- 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.
- 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.
- 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.
|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.