The Looping Containers

There are two looping containers in Integration Services. The For Loop is the simpler of the two and iterates over workflow based on expressions. This container is useful when you need to iterate a certain number of times or until a certain condition exists. The Foreach Loop is the more complex and makes it possible to iterate over workflow for each item in a collection. This container is useful when you have a list of inputs that you want to apply to the same workflow.

The Foreach Loop and For Loop support transactions in the same way as the Sequence container. They commit all operations if the container completes successfully and roll back all operations if not.

The LoopContainers package in the S07-Containers sample solution illustrates this with a Foreach Loop that performs the same function as the SequenceContainers package. The Foreach Loop has a Foreach Item Enumerator with the values to be entered into the table. The last item has a nonunique ID that causes the insert to fail and, consequently, all the inserts to roll back. The difference is, because the table is created outside the Foreach Loop, it's not in the transaction and remains even when the transaction gets rolled back. To read more about the looping containers, see Chapter 13, "Looping and Foreach Enumerators," where they're covered in detail.