Use the SSIS foreach loop container-foreach item enumerator

The foreach loop container is a very common control flow task in the SSIS structured control flow design. It is used to traverse all objects in a set and then perform the same operation. Its function is similar to the structured control flow design.ProgramIn Design
Foreach I in Set
...
End structure. In SSIs, the following sets can be defined:

Project set (foreach item enumberator)
File set (foreach file enumberator)
Foreach ADO enumberator)
Table structure set (foreach ADO. Net schema enumberator)
Set of variable values (foreach from variable enumberator)
XML Node Set (foreach nodelist enumberator)
SQL Server Management object collection (foreach SMO enumberator)

Foreach item enumberator
The most common use is to traverse the database on the server and perform the same operations on the tables in the database. For example, I have 10
Different databases are distributed on different database servers. Now we need to perform a similar operation on one of the 10 databases: Clear
. Of course there are many ways to achieve this, but in my example, I use the foreach loop container to implement it.

• First, create a new SSIS package
• Create two system variables: databasename and servername. These two variables are used to store the server name and database name represented in the item set in the foreach loop.
• Create an oledb data connection and use Windows authentication. Make sure that the execution user of the SSIS package has the DML permission for execution in each database. use expressions to dynamically generate connection strings. (Refer to my other article.Article: Http://www.bridata.ca/blog? P = 216
  How to use dynamic database connection)
• Place a foreach loop container on the control flow page and double-click the editor;
• Select foreach item enumberator under enumerator in the collection panel;
• At this time, the enumertor configuration area will appear below the window. Click the columns button to add two items
  Fields in, all of which belong to the string type. These two fields are used to store the server name and database name respectively. Note: The field name cannot be modified. The default value is
  Column 0, column 1 ....
• In column 0 and column 1, enter the server name and database name, as shown in:

• Click collection on the screen and select variable ing, as shown in,
  The indexes of user variables servername and databasename are 0 and 1, that is, column0 and column1
  Is a ing relationship, which is used to store values in the item set in the foreach loop. For example, in this example, the item set contains 10
  Row data, that is, there are 10 cycles in the operation. For each loop, the values of our user variables servername and databasename are based on the item
  The value in the set is changed.

• Add an execute SQL task to the foreach loop container and set its connectiontype to Ole
  DB, connection is the newly established oledb connection. in SQL statement, enter: truncate
  Table log. Shows some SSIS packages:

Because the oledb connection is established based on variables, and the variables change dynamically in the foreach item loop, the connection also changes dynamically.