Foreach Loop Container: looping through result set of a database query

Foreach Loop Container is one of the two loop structures in the SSIS Control Flow, and it can be said strongly that this is the most useful. The Foreach Loop provides a method of looping through seven different types of enumerators and can loop through different types of collections. Collection types vary from files in a folder to nodes in xml data or records in a database table.

In this recipe, we will take a closer look at the ADO enumerator and one of the most useful real world scenarios of using this container.

Suppose that we want to loop through different SQL Server database servers, each server having a different server name and different database name. We read list of all tables with their schema from each database. We want to use a single Data Flow to extract data from an OLE DB Source and load it into an Excel destination, and we need to run this Data Flow for each server and append all the data from all servers...