The Foreach Loop container enables a package to repeat the control flow for each member of a specified enumerator.
Currently, the Flat File connection manager connects to only one specific flat file. To iteratively connect to each flat file in the folder, you have to configure both the Foreach Loop container and the Flat File connection manager as follows:
ConnectionStringproperty of the Flat File connection manager and iteratively connect to each flat file in the folder.
In the SSIS Toolbox, expand Containers, and then drag a Foreach Loop Container onto the design surface of the Control Flow tab.
Right-click on the new Foreach Loop Container and select Edit.
In the Foreach Loop Editor dialog, on the General page, enter Foreach File in Folder in the Name field.
Click on the OK button. Now right-click the Foreach Loop container, select Properties, and in the Properties window verify that the LocaleID property is set to English (United States).
To configure the enumerator, double-click on the Foreach File in Folder. It will open the Foreach Loop Editor dialog and go to the Collection page.
On the Collection page, select Foreach File Enumerator.
In the Enumerator configuration group, select the Browse button and locate the folder on your machine that contains the Currency_*.txt files included with the sample data.
In the Files box, enter Currency_*.txt.
Now go to the Variable Mappings page, and click on the empty cell in the Variable column.
In the Add Variable dialog box, enter
varFileName in the Name field and click the OK button.
Select the OK button again to exit the Foreach Loop Editor dialog.
To add the data flow task to the loop, drag the Extract Sample Currency Data data flow task onto the Foreach File in Folder Foreach Loop container.