Chapter 43

Using the Foreach Loop Container to Loop Through a Collection of Objects

The Foreach Loop Container is a very powerful and very useful tool for repeating Control Flow items. It is often used when you have a collection of files to which you want to apply the same changes. If you provide the directory for a set of files, the Foreach Loop Container can apply the same Control Flow tasks to each file. You might ask yourself, how is this different from the For Loop Container? The easy answer is that the For Loop iterates through the content of the container a number of times you define or you define with an expression, whereas the Foreach Loop Container iterates through its content as many times as it takes to effect the full collection.

The configuration of the Foreach Loop Container can differ depending on which enumerator you decide to use. An enumerator specifies the collection of objects that the container will loop through. All tasks inside the container will be repeated for each member of a specified enumerator. The Foreach Loop Editor can significantly change depending on what you set for this option:

  • Foreach File Enumerator—Performs an action for each file in a directory with a given file extension
  • Foreach Item Enumerator—Loops through a list of items that are set manually in the container
  • Foreach ADO Enumerator—Loops through a list of tables or rows in a table from an ADO recordset
  • Foreach ADO.NET Schema Rowset Enumerator—Loops through an ADO.NET schema
  • Foreach From Variable Enumerator—Loops through a SQL Server Integration Services (SSIS) variable
  • Foreach Nodelist Enumerator—Loops through a node list in an XML document
  • Foreach SMO Enumerator—Enumerates a list of SQL Management Objects (SMO)

To configure the Foreach Loop Container, drag it on the design surface and double-click the top portion of the container to open the editor. Click the Collection tab to choose which type of enumerator you want to use, as shown in Figure 43-1. For example, say you want to loop through a directory to load a group of flat files to a table, so you choose the Foreach File Enumerator. Then you must specify what folder directory the files are in and what kind of file extension they have. Assume the flat files are .txt files, so in the Files box *.txt is used to bring back only the text files in the directory. Last, because the flat files each have a different name, you can use the Variable Mappings tab to dynamically change a variable value for each iteration of the loop. That variable then can pass the correct file name to the Flat File Connection with an expression. Don’t worry if this explanation sounds complicated because the following “Try It” section gives you a step-by-step example of how to do this exact scenario.

Figure 43-1

c43f001.tif

Another commonly used enumerator is the Foreach ADO Enumerator. This enumerator is handy for looping through a set of records and executing every task inside the container for each record in that set. For example, you want to run each task in your package for every database on a server. With the Foreach ADO Enumerator, you could loop through a table that lists all the database names on your server and dynamically change a connection manager’s database name for each iteration of the loop.

Try It

In this Try It, you create a package that uses the most common type of enumerator, the Foreach File Enumerator, to loop through a collection of flat files and load them to a table. After this lesson, you will understand how to use the Foreach Loop Container to loop through a collection of files and load each to a table.

You can download the completed Lesson43.dtsx and the sample files used in this lesson from www.wrox.com.

Lesson Requirements

Download the four flat files named File 1.txt, File 2.txt, File 3.txt, and File 4.txt from www.wrox.com to use as your source. Save these files to the C:ProjectsSSISPersonalTrainerLesson 43 directory.

Create a table named ForEachLoop in the AdventureWorks2012 database to load each flat file into.

Use a Foreach Loop Container to loop through and load each file in the C:ProjectsSSISPersonalTrainerLesson 43 directory.

Hints

  • Create a variable to store the location of the file that currently needs to be loaded. The loop will change the variable location after each run.
  • Use this variable as an expression for the connection manager that points to the flat file.

Step-by-Step

1. Create a new package and name it Lesson43 or download the completed Lesson43.dtsx package from www.wrox.com.
2. Drag a Data Flow Task onto your designer and name it DFT - Load Flat Files.
3. Create a new Flat File Connection Manager, name it File Extract, and point it to File 1.txt in the following directory: C:ProjectsSSISPersonalTrainerLesson 43File 1.txt. Also, check the Column names in the first data row option and go to the Columns page to ensure all the columns are defined properly; then click OK.
4. In the Data Flow, bring a new Flat File Source over and name it File Extract. Open the Flat File Source Editor by double-clicking the Flat File Source and make the connection manager the newly created File Extract. Then click OK.
5. Next, create another connection manager, this time an OLE DB Connection Manager, using the AdventureWorks2012 database.
6. Bring an OLE DB Destination in the Data Flow and connect the Data Flow path from the source to it. Open the editor and set the OLE DB Connection Manager to AdventureWorks2012. Create a new table with the following SQL statement by clicking New next to the table selection drop-down box: 
CREATE TABLE [ForEachLoop] (
    [Name] varchar(50),
    [State] varchar(50)
)
Ensure the columns are mapped correctly; then click OK. Your Data Flow should now look like Figure 43-2.

Figure 43-2

c43f002.tif
7. Your package is now set up to run just one file, but because you have four, you now go back to the Control Flow and drag over a Foreach Loop Container.
8. Place the Data Flow Task inside the Foreach Loop Container; then open the Foreach Loop Editor by double-clicking the top banner portion of the container. On the Collections tab, select Foreach File Enumerator from the Enumerator property drop-down box. The Foreach File Enumerator is the default when you open the editor.
9. Now, set the Folder property to the C:ProjectsSSISPersonalTrainerLesson 43 directory and the Files property to *.txt because you want to bring back all the text files in the directory. Everything else you can leave as the default. After you make these changes, your editor should look like Figure 43-3.
10. On the Variable Mappings tab create a new variable called strFlatFileLocation by selecting <New Variable…> from the Variable drop-down box. Figure 43-4 shows the Add Variable dialog box.

Figure 43-3

c43f003.tif

Figure 43-4

c43f004.tif
11. This variable’s value will change to the current file it is loading each time the container runs. In this specific case, after File 1.txt is completed, the container will automatically change the variable’s value to the next filename. After the variable is created, click OK. The Variable Mappings tab should look like Figure 43-5. Click OK again to return to the Data Flow.

Figure 43-5

c43f005.tif
12. The last step is to put an expression on the File Extract Connection Manager that uses the variable you just created inside the Foreach Loop Container. Select the File Extract Connection Manager called File Extract from your list of connection managers and press F4 to bring up the Properties window. Click the ellipsis next to the Expressions property to open the Property Expressions Editor. Select ConnectionString from the Property drop-down and then click the ellipsis in the Expression box.
13. In the top left of the Expression Builder, expand the Variables and Parameters folder and drag @[User::strFlatFileLocation] down into the Expression box. If you try to click Evaluate Expression now, there will be no result. Remember that this expression will be populated at run time, so you will see nothing here yet. Your screen should look like Figure 43-6. Click OK twice to return to the Data Flow.
14. The package is now complete. A successful run will loop through and load all the files in the C:ProjectsSSISPersonalTrainerLesson 43 directory to the ForEachLoop table in the AdventureWorks2012 database. Figure 43-7 shows what a successful run should look like.

Figure 43-6

c43f006.tif

Figure 43-7

c43f007.tif
Please select Lesson 43 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset