It seems I am often helping people configure SSIS packages to loop over several flat files to extract data to be sent to a single SQL destination table. While some may have found a way to accomplish this in the old DTS, the For Each loop in SSIS 2005 makes this much easier. I hope that this post will get you started on this powerful task.
DISCLAIMER: Please take the time to understand everything that is going on with the package you design from this blog. Fortunately, it does not contain any blocks of code that you can copy and paste into your desinger to make it all work for you. I say “Fortunately” because it is fortunate for me. If it did contain such code, I would also be obliged to warn you about blindly copying and pasting code from the internet into your application without fully understanding what it does.
One of the most common scenarios is when data comes in from various scources in flat file format (.txt, .csv, or some other format) and needs to be loaded into SQL Server. It is fairly easy to set up a single flat file source in SSIS, but there are a couple of extra things to watch for when trying to loop over several of them.
Start with a single file and a working Data Flow
The first thing to do is create a Flat File Connection Manager to be used as the Source. Point it to ONE FILE in the folder that contains ALL the flat files you will be using. Configure the rest of the Data Flow to use this one Flat File. When designing packages such as this, I usually start small and build from there, getting the one file to work first, making sure the column mappings on the destination are correct ext, before adding the Looping container.
Add the Loop:
After you have a working Data Flow for one file, drag a ForEach loop onto the Control Flow. Then drag and drop your working Data Flow task into it. For the loop container, set the Enumerator (on the Collections page) to “Foreach File Enumerator”. Choose your Folder and select to retrieve Fully qualifield file names. (See below, even if the screen shot is fuzzy)
Click the Variable Mappings page. Create a mapping to a new variable which we’ll call FileName (with a Data Type of String). Note that the Index for this should be 0 (zero). That’s about all you need for this task.
The Flat File Connection Manager
The last thing to do is configure the Flat File Connection Manager to make it smart enough to switch to the next file in synch with the loop. Highlight it in the Connection Managers tray, then find Expressions in the Properties pane. Create a new Expression here. Select ConnectionString for the property, and set it equal to the FileName variable.
Here’s what’s going to happen at RunTime: The For Each loop will look at the first file in the folder specified and load the fully qualified name into the FileName Variable. The Flat File Connection Manager will then use that string to connect to the appropriate file in the folder.
Bells and Whistles
That’s really all there is to get this to work. However, as is typical, there are lots of other bells and whistles you can add to help manage your files. One common task is to have the completed files moved to a different folder after they have been processed so that when NEXT month’s data arrives, you won’t get collisions.