How to configure a Foreach Loop Container: ADO Enumerator

Case
I have a list of files in a database table which I want to loop through. Can I use a Foreach Loop Container for that?

My table with files

Solution
You can use the Foreach Loop Container with a Foreach ADO Enumerator for this task.


1) Variables
Create two variables. A variable named myFiles of the Object data type. This will contain the list of files. A string variable named filePath. This variable will be used in the Foreach Loop Container and will contain a single filepath.

Variables

2) Execute SQL Task - General
Add an Execute SQL Task to the Control Flow and edit it. Select Full result set as ResultSet; Select the Connection Manager to connect to the database with your table and add the SQLStatement.

Execute SQL Task - Editor - General

3) Execute SQL Task - Result Set
Go to the Result Set pane and click add. Set the Result Name to zero and select the Object variable from step 1.

Execute SQL Task - Editor - Result Set

4) Foreach Loop Container - Collection
Add a Foreach Loop Container and connect it to your Execute SQL Task. Edit the Foreach and go to the Collection pane. Select the Foreach ADO Enumerator and select the Object variable from step 1 as the ADO object source variable.

Foreach Loop Container - Collection

5) Foreach Loop Container - Variable Mappings
Go to the Variable Mappings pane and select the string variable from step 1. The index should be zero. It's the first column in a zero based column index (second column has index 1 and so on).


Foreach Loop Container - Variable Mappings

6) The Result
Now the construction is ready. The variable filePath will be filled with a filepath from the query. You can for example use this variable in an expression on a Flat File Connection Manager or in a File System Task. If you want to know how the expression on the connection manager works. Then go to this post about the File Enumerator and start at step 3. I added a Script Task with a messagebox to test the foreach loop.


MessageBox.Show(Dts.Variables["User::filePath"].Value.ToString());

Note: I have an other Foreach ADO Enumerator example that uses a Script Task to fill the object variable instead of the Execute SQL Task.