Adding and Configuring the Foreach Loop Container

In this task, you will add the ability to loop through a folder of flat files and apply the same data flow transformation used in Lesson 1 to each of those flat files. You do this by adding and configuring a Foreach Loop container to the control flow.

The Foreach Loop container that you add must be able to connect to each flat file in the folder. Because all the files in the folder have the same format, the Foreach Loop container can use the same Flat File connection manager to connect to each of these files. The Flat File connection manager that the container will use is the same Flat File connection manager that you created in Lesson 1.

Currently, the Flat File connection manager from Lesson 1 connects to only one, specific flat file. To iteratively connect to each flat file in the folder, you will have to configure both the Foreach Loop container and the Flat File connection manager as follows:

• Foreach Loop container   You will map the enumerated value of the container to a user-defined package variable. The container will then use this user-defined variable to dynamically modify the ConnectionString property of the Flat File connection manager and iteratively connect to each flat file in the folder.
• Flat File connection manager   You will modify the connection manager that was created in Lesson 1 by using a user-defined variable to populate the connection manager's ConnectionString property.

The procedures in this task show you how to create and modify the Foreach Loop container to use a user-defined package variable and to add the data flow task to the loop. You will learn how to modify the Flat File connection manager to use a user-defined variable in the next task.

After you have made these modifications to the package, when the package is run, the Foreach Loop Container will iterate through the collection of files in the Sample Data folder. Each time a file is found that matches the criteria, the Foreach Loop Container will populate the user-defined variable with the file name, map the user-defined variable to the ConnectionString property of the Sample Currency Data Flat File connection manager, and then run the data flow against that file. Therefore, in each iteration of the Foreach Loop the Data Flow task will consume a different flat file.

To add a Foreach Loop container

1. In Business Intelligence Development Studio, click the Control Flow tab.

2. In the Toolbox, expand Control Flow Items, and then drag a Foreach Loop Container onto the design surface of the Control Flow tab.

3. Right-click the newly added Foreach Loop Container and select Edit.

4. In the Foreach Loop Editor dialog box, on the General page, for Name, enter Foreach File in Folder. Click OK.

5. Right-click the Foreach Loop container, click Properties, and in the Properties window, verify that the LocaleID property is set to English (United States).

To configure the enumerator for the Foreach Loop container

1. Double-click Foreach File in Folder to reopen the Foreach Loop Editor.

2. Click Collection.

3. On the Collection page, select Foreach File Enumerator.

4. In the Enumerator configuration group, click Browse.

5. In the Browse for Folder dialog box, locate the sample data folder that contains the tutorial sample data.

   By default, the tutorial sample data is installed to the folder C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data.

6. In the Files box, type Currency_*.txt.

To map the enumerator to a user-defined variable

1. Click Variable Mappings.

2. On the Variable Mappings page, in the Variable column, click the empty cell and select <New Variable…>.

3. In the Add Variable dialog box, for Name, type varFileName.

   Important

   Variable names are case sensitive.

4. Click OK.

5. Click OK again to exit the Foreach Loop Editor dialog box.

To add the data flow task to the loop

• Drag the Extract Sample Currency Data data flow task onto the Foreach Loop container now renamed Foreach File in Folder.

Next Lesson Task

Modifying the Flat File Connection Manager

See Also

Other Resources

How to: Configure a Foreach Loop Container
Using Variables in Packages

Help and Information

Getting SQL Server 2005 Assistance

Change History