SSIS Foreach Loop ADO Enumerator

The Foreach loop Container ADO enumerator iterates over the Database objects. This SSIS Integration Services article shows the step in configuring the SSIS Foreach loop ADO enumerator to transfer records from One SQL Database to another with an example.

To demonstrate this, we use the Employee Table in our database.

Drag and Drop the Execute SQL Task into the SSIS Control Flow Region. Next, create variables to hold the records. As the Employee table has six columns (except ID), we made six variables and an extra one of an Object type to store the complete result set.

Double-click the Execute SQL Task, change the ResultSet to Full result set, and choose the Connection from the Existing one. Next, click the browse button to write the SQL statement.

Assign the User::ResultSet variable name to the result set.

SSIS Foreach Loop ADO Enumerator Example

Drag and drop the Foreach loop container into the control flow region.

Double-click on it will open the editor. First, change the Loop Enumerator type to SSIS Foreach ADO Enumerator. Next, select the User::ResultSet as the ADO object source variable.

For the remaining iterators,

1. ADO.NET Schema Rowset Enum
2. File Enumerator
3. Item Enumerator
4. NodeList Enumerator
5. SMO Enumerator
6. Variable Enumerator

Choose the Enumeration mode. Here, we chose the Rows in the First table.

Under the Variable mappings tab, choose the variables that we created earlier. Here, the Index order has to match the Table columns.

Add the Execute SQL Task into the Foreach loop ADO Enumerator container.

Double-click to open the editor and select the Connection to store the records. We used the INSERT statement within the SQL Statement to load records with parameters.

INSERT INTO [dbo].[ADO Enumerator]
           ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
     VALUES (?, ?, ?, ?, ?, ?, ?)

Go to the Parameter Mapping tab, assign variable names, and change the Data type to match the destination table.

Run the SSIS Foreach Loop ADO Enumerator package.

Open SQL Management Studio to see the output.