SSIS Foreach Loop ADO Enumerator

The Foreach loop Container ADO enumerator iterate 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.

Table

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.

Execute SQL Task

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.

Execute SQL Task Editor

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

Add variable name to the result set

SSIS Foreach Loop ADO Enumerator Example

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

SSIS ForEach Loop ADO Enumerator 6

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.

SSIS ForEach Loop ADO Enumerator Object Variable

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

Choose Mode for SSIS ForEach Loop ADO Enumerator

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

SSIS ForEach Loop ADO Enumerator Variabel Mapping

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

Add Execute SQL Task

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 (?, ?, ?, ?, ?, ?, ?)
SQL Insert Statement

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

Parameter Mapping

Run the SSIS Foreach Loop ADO Enumerator package.

Run SSIS ForEach Loop ADO Enumerator package

Open SQL Management Studio to see the output.

SSIS ForEach Loop ADO Enumerator Output