Container Tasks in SQL Server 2008 Integration Services

Foreach Loop Container

Use Foreach Loop Container Task to loop through a collection of objects. In SQL Server 2005 and later versions there are 7 different types of Foreach Loop Containers.

1.       Foreach File Enumerator

2.       Foreach ADO Enumerator

3.       Foreach ADO.Net Schema Rowset Enumerator

4.       Foreach Item Enumerator

5.       Foreach Nodelist Enumerator

6.       Foreach From Variable Enumerator

7.       Foreach SMO Enumerator

Example - Foreach File Enumerator

 We will work through an example using the Foreach File Enumerator to move all the system databases related backup files to “C:\DDBackups\SystemDatabaseBackups” folder from “C:\DBBackups” common database backup folder.

 1.       Create a new SQL Server Integration Services Project and rename the default package ForeachFileEnumerator.dtsx

2.       Double click ForeachFileEnumerator.dtsx package to open it up in Design mode.

3.       Add a variable named “DatabaseBackupFiles”; you can open up Variables window by right clicking within the control flow design window and by choosing the Variables option from the drop down list.

4.       Once the variables window is open, click on Add Variable button and provide the details as shown in the below snippet.

5.       Drag and drop “Foreach Loop Container” from the toolbox to the Control Flow window. Double click the for loop container to open up editor window as shown in the below snippet.

Next, you need to set the Enumerator option to Foreach File Enumerator from the drop down list. Then, set the folder property to “C:\DBBAckups” and set the value to “M*.*” for Files. And finally under retrieve file name select “Fully Qualified”.

6.       Within the Variable Mapping Page in the Foreach Loop Container, select the "User::DatabaseBackupFiles" variable which we created initially from the drop-down list and then accept the default value of 0 for the index as shown in the below snippet. Click OK to save the settings.

7.       Next step will be to drag and drop a new File System Task into the Foreach Loop Container. Then, double click the File System Task to open up the File System Task Editor as shown in the below snippet.

Configure the Destination Connection by selecting <New Connection…> from the DestinationConnection property. This opens up the File Connection Manager Editor as shown in the below snippet. In the File Connection Manager Editor window select Existing Folder for Usage Type option and set the folder location to “C:\DDBackups\SystemDatabaseBackups”. Click OK to save the settings.