31 DAYS OF SSIS – JIM CROCE AND THE FOREACH (FILE) LOOP CONTAINER (28/31)

This time we have Mark Vaillancourt (Blog | @markvsql) taking over the reigns.  As with the other two guest bloggers, he’s a co-worker at Digineer and is on the team with me that works with SSIS and on SQL Server projects.

Enough on the introduction, on with the show…

GUEST BLOGGER

When Jason Strate (blog | Twitter) offered spots for guest bloggers during his 31 Days of SSIS, I jumped at the chance. Yet more mentorship from Mr. Strate. And when I decided to blog about advantages of using the Foreach (File) Loop Container, I immediately thought of the late Jim Croce. I’m sure you did, too, right? I know; it’s pretty obvious.

Mr. Croce has a song called You Don’t Mess Around With Jim that makes a more than suitable example for this post. This great song tells the story of “Big Jim Walker,” a local tough guy that no one wanted to mess with. The chorus pretty much sums it up:

You don’t tug on Superman’s cape
You don’t spit into the wind
You don’t pull the mask off that old Lone Ranger
And you don’t mess around with Jim

So, you get the idea that the name of the toughest guy around is going to “Jim” for some time to come.

It is rather like working on an SSIS project to import a file and being told that the name of the import file will always stay the same. Do you actually build the package with the name of that file hard coded in the Connection? A better option would be to put that name in a configuration file so it is easy to change if you get notified to do so. But neither of these will allow your solution to continue without error if the name of the file does not match what is expected. Thus, your solution is more fragile than it needs to be.

In situations like these, I use the Foreach Loop Container configured with the Foreach File Enumerator. I learned it from Aaron Drinkwine (Twitter), a colleague at Digineer. Aaron was the guest blogger on 1/14 in this series.

This method has excellent advantages which include the following:

1. You can have the Foreach File Enumerator look for a pattern instead of a full name. For example, you could have it look for all .txt files in the selected folder. Or you could even configure it with part of a file name. This allows your solution to dynamically deal with filenames that are unexpected as long as they match the overall pattern.

2. The container includes functionality for dynamically capturing in a variable the actual name of the file that was read. You can then use this in your logging, notifications, emails, etc.

I typically then use this value in expressions to build the necessary strings involved in archiving the file (using the File System Task) after import. I learned this from Aaron, too. Jason discusses how to have variables derive their value from other variables using expressions in post 5 of this series: 31 Days of SSIS – One Variable To Rule Them All (5/31).

If you don’t archive import files in your solutions, I highly recommend it. I have been in situations where I built an SSIS package for a client that was importing from a third party. There were frequent data quality issues that the third party consistently tried to blame on my process. Each time, I was able to send them their own file showing that the issues came from their data, not the import process itself. This helped what was already a strong relationship with this client to get even stronger.

Before we go too far, let’s create a package variable called ImportFileName with its type set to String. The Scope of our variable is set to the package, which is called ForeachFileLoop.

Let’s take a look at the Foreach Loop Container and go through a configuration example. The General page of the Foreach Loop Editor allows you to customize the name of the container as well as add a description if you wish.

The Collection page is where you specify some crucial options.

You must choose an Enumerator, which determines what kind of object your container will iterate through. The Foreach File Enumerator, which we will be using today, is the default.

The Foreach File Enumerator has the following options on the Collection page:

Folder: This is the target folder that will be searched. I have created one called YouDontMessAroundWith.

Files: This is where you set the pattern the enumerator will use in determining whether to pick up the file for processing.

Retrieve File Name: This determines how much of the file name and path are captured for later use. For our purposes, just the Name will be sufficient.

Traverse Subfolders: This allows you to choose whether the enumerator will include subfolders of the target folder when searching. Since our target folder has no subfolders, we will leave this option alone.

On the Variable Mappings page of the Foreach Loop Editor, you choose what variable(s) will be used. For our example, we will select our ImportFileName variable to house the file name captured. Notice that it is referred to as User::ImportFileName.

Our simple example will not be using the Expressions page of the Foreach Loop Editor, so we will leave that alone.

Since Jim is the man people don’t mess with, I have created a file called Jim.txt in the YouDontMessAroundWith folder. This means when our package runs, the value of the ImportFileName variable will be set to “Jim” for later use.

I added a quick Script Task inside my Foreach Loop Container that just displays the value of the User::ImportFileName variable in a message box. When I execute my package, I get the following:

In the song, another fella, Slim, comes around and it doesn’t go well for Jim. In fact, it goes so poorly that the chorus of the song changes.

You don’t tug on Superman’s cape
You don’t spit into the wind
You don’t pull the mask off that old Lone Ranger
And you don’t mess around with Slim

To correspond with this unexpected change, I have replaced my Jim.txt file with Slim.txt. Now, if I had just hard coded the file name, or even used a config file, my package would fail.

Since I used the Foreach Loop Container, I get the following when running the same exact package with the new file.

We have a resilient, flexible solution with very little effort. So, we can even write our own version of the chorus that could be used throughout the entire song.

You don’t tug on Superman’s cape
You don’t spit into the wind
You don’t pull the mask off that old Lone Ranger
And you don’t mess around with User::ImportFileName