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.

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

You need to now map the variables to the results of the Foreach File Enumeration. Go to the Variable Mappings page inside the Foreach Loop Editor and select <New Variable…> from the Variable column drop-down box. This will open the Add Variable dialog box. For the container, you'll remain at the package level. You could assign the scope of the variable to the container, but you should keep things simple for this example. Name the variable "ExtractFileName" in the Name option and click OK, leaving the rest of the options at their default settings.

You will then see the User::ExtractFileName variable in the Variable column and the number 0 in the Index option. Since the Foreach File Enumerator option has only one column, you'll only see an index of 0 for this column. If you used a different enumerator option, you would have the ability to enter a number for each column that was returned from the enumerator. Click OK to leave the Foreach Loop editor.

Making the Package Dynamic

Now with the loop created, you need to set the file name in the Corporation Extract Connection Manager to be equal to the file name that the enumerator retrieves dynamically. To meet this business requirement, right-click on the Corporation Extract Connection Manager and select Properties (note that you're clicking on Properties, not on Edit as you've done in the past). In the Properties pane for this Connection Manager, click the ellipsis button next to the Expressions option.

By clicking the ellipsis button, you open the Property Expressions Editor. Select ConnectionString from the Property drop-down box, as shown in Figure 5-16. You can either type in @[User::ExtractFileName] in the Expression column or click the ellipsis button and then drag and drop the variable into the expression window. By typing @[User::ExtractFileName], you are setting the file name in the Connection Manager to be equal to the ExtractFileName variable that you set in the Foreach Loop earlier. Click OK to exit the Property Expression Editor. You'll now see in the Property window that there is a single expression by clicking the plus sign.

Figure 5-16

As it stands right now, each time the loop finds a .DAT file in the C:\SSISDemos directory, it will set the ExtractFileName variable to that path and file name. Then, the Connection Manager will use that variable as its file name and run the Data Flow task one time for each file it finds. You now have a reusable package that can be run against any file in the format you designated earlier.

The only missing technical solution to complete is the archiving of the files after you load them. Before you begin solving that problem, manually create an archive directory under C:\SSISDemos called C:\SSISDemos\Archive. Right-click in the Connection Manager window and select Create New File Connection. Select Existing Folder for the Usage Type, and point the file to the C:\SSISDemos\Archive directory. Click OK and rename the newly created Connection Manager "Archive File."

Next, drag a File System task into the "Loop Through Files" container and connect it to the "Load Corporate Data" Data Flow task with an On Success constraint (the green arrow should be attached to the File System task). Rename that task "Archive File."

Double-click on the "Archive File" File System task to open the editor (shown in Figure 5-17). Set the Operation drop-down box to Move File. Next, specify that the Destination Connection not be a variable and that it be set to the Archive File Connection Manager that you just created. The SourceConnection drop-down box should be set to the "Corporation Extract" Connection Manager that you created a long time ago. Essentially, what you're configuring is that the file that was pulled earlier from the loop will be moved to whatever directory and file name is in the Archive File Connection Manager.

Figure 5-17

Your complete package should now be ready to execute. Go ahead and save the package first before you execute it. If you successfully implemented the solution, your control flow should look something like Figure 5-18 when executed. When you execute the package, you'll see the control flow items flash green once for each .DAT file in the directory.

Figure 5-18