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.
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.
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.
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.