Professional SQL Server™ 2005 Integration Services

5.3. Looping and the Dynamic Task

You've come a long way in this chapter to creating a self-healing package, but it's not terribly reusable yet. Your next task in the business requirements is to configure the package so that it reads a directory for any .DAT file and performs the previous tasks to that collection of files. To simulate this example, go ahead and download a few files again from ftp://dossftp.dos.state.fl.us/public/doc/cor/. They can be whatever .DAT file you'd like, but make sure you download at least two more.

5.3.1. Looping

Your first task is to loop through any set of .DAT files in the C:\SSISDemos directory and load them into your database. To meet this business requirement, you'll need to use the Foreach Loop container. Go to the Control Flow tab in the same package that you've been working in, and drag the container onto the design pane. Then, drag the "Load Corporate Data" Data Flow task onto the container. Rename the container "Loop Through Files."

Double-click on the container to configure it. Go to the Collection page and select Foreach File Enumerator from the Enumerator drop-down box. Next, specify that the folder will be C:\SSISDemos and that the files will have the *.DAT extension, as shown in Figure 5-15.

Figure 5.15. Figure 5-15

You need to now map the variables to the results of the Foreach File Enumeration. Go to the Variable Mappings page inside ...