Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 43

Using the Foreach Loop Container to Loop Through a Collection of Objects

The Foreach Loop Container is a very powerful and very useful tool for repeating Control Flow items. It is often used when you have a collection of files to which you want to apply the same changes. If you provide the directory for a set of files, the Foreach Loop Container can apply the same Control Flow tasks to each file. You might ask yourself, how is this different from the For Loop Container? The easy answer is that the For Loop iterates through the content of the container a number of times you define or you define with an expression, whereas the Foreach Loop Container iterates through its content as many times as it takes to effect the full collection.

The configuration of the Foreach Loop Container can differ depending on which enumerator you decide to use. An enumerator specifies the collection of objects that the container will loop through. All tasks inside the container will be repeated for each member of a specified enumerator. The Foreach Loop Editor can significantly change depending on what you set for this option:

• Foreach File Enumerator—Performs an action for each file in a directory with a given file extension
• Foreach Item Enumerator—Loops through a list of items that are set manually in the container
• Foreach ADO Enumerator—Loops through a list of tables or rows in a table from an ADO recordset
• Foreach ADO.NET Schema Rowset Enumerator—Loops through an ADO.NET schema
• Foreach ...