Using the SSIS Foreach Loop container –foreach Item Enumerator

The Foreach Loop container is a very common control flow task in the design of the SSIS structured control flow that iterates through all the objects in a collection, and then performs the same operation, which functions like the one in structured programming.
Foreach I in Collection
...
END structure, in SSIS these can be defined collections include:

Project collection (Foreach item enumberator)
Files collection (Foreach file enumberator)
Record Collection (Foreach ADO enumberator)
Table Structure Collection (Foreach ado Schema enumberator)
Variable Value collection (Foreach from Variable enumberator)
XML node collection (Foreach Nodelist enumberator)
SQL Server Management Object collection (Foreach SMO enumberator)

One of the most common uses of Foreach Item Enumberator is to traverse the database in the server and perform the same operations on the tables in the database. For example, I have 10 different databases distributed on different database servers, and now we need to do a similar operation on one of the 10 databases: Clear the contents of a table called Log. Of course there are many ways to do this, but in my case I use the Foreach Loop container to implement it.

• Start by creating a new SSIS Package
• Establish two system variables: DatabaseName and ServerName, which will be used to store the server name and database name represented in the item collection in the Foreach Loop
• Establish an OLE DB data connection, use Windows authentication, and make sure that the execution user of the SSIS package has permission to perform the appropriate DML on each database, and that the connection string is dynamically generated using an expression. (Refer to my other article: http://www.bridata.ca/blog/?p=216 How to use database dynamic connections)
• Place a foreach Loop Container on the Control Flow page and double-click to open the editor;
• Select the Foreach Item enumberator under Enumerator in the Collection panel;
• The Enumertor Configuration area appears at the bottom of the window, click the Columns button, add the fields from the two items, their data types are all String types, and the two fields are used to store the server name and database name, respectively. Note: The name of the field cannot be modified, the system default is column 0, column 1 ....
• Enter the server name and the database name in column 0 and column 1, as shown in:

• Click Collection on the screen to select the variable mappings, as shown in the index of the user variables servername and DatabaseName are 0 and 1 respectively, that is, the Column0 and Column1 in is a mapping relationship that they use to store the Foreach The value in the item collection in Loop. For example, in this case, there are 10 rows of data in the Item collection, that is, there are 10 loops in the run, each cycle, and the values of our user variables ServerName and DatabaseName are changed according to the value in the item collection.

• Add an Execute SQL Task to the foreach Loop container, set its connectiontype to OLE DB, Connection to the newly-created DB Connection, and in SQL statement into: Truncate Table Log. Part of the SSIS package looks like this:

Because the OLE DB connection is based on a variable, and the variable is dynamically changed in the loop of the foreach Item, the connection changes dynamically.

Using the SSIS Foreach Loop container –foreach Item Enumerator