Writing file names to a SQL Server table in SSIS

I'm just creating our SSIS course, and thought I'd take a little break from writing courseware to writing blogs!

This blog shows you how to do a very specific thing in Integration Services: that is, create a table of all of the file names in a folder:

What I'm trying to achieve - a list of all of the web log files in a particular folder.

Creating the loop over files

The first thing to do is to create a new package in an SSIS project, and create a Foreach Loop task in it:

Drag this task onto your package.

Now double-click on the task (or right-click on it) to edit it:

Right-click on the task to edit it, or just double-click on it.

Go to the Collection tab of the dialog box which appears:

Click on this tab to specify whether you're looping over files, records or anything else for that matter.

Now complete the numbered steps shown in the diagram below:

1. Choose to loop over files (it's the default, so you won't need to change anything here).
2. Click on the Browse... button to choose the folder containing your files.
3. Choose which files you want to loop over (here we've chosen all files with extension .log).
4. Choose how much of the file name and path you want to bring back.

Here's a diagram showing these steps:

Follow the numbered steps shown above, then select OK.

Finally (for this task), choose to store the file name each time round the loop in a variable:

In the Variable Mappings tab, click to create a new variable.

Here I've called my variable FileName:

The variable will be in the User namespace.

Adding the SQL Task

I've cheated a bit for this, and created a table in SQL Server to hold my file names:

A thousand characters should be enough to hold the longest file name, don't you think?

First add an Execute SQL Task to your Foreach Loop container:

Drag this task from the SSIS toolbox onto the Foreach Loop container.

Here's what you should now see:

The result: SSIS will execute this SQL task for each file in the given folder.

Double-click on the Execute SQL Task, and create the following SQL command:

The SQL statement to execute.

Here's what this SQL statement looks like:

You now need to associate the variable containing the file name with the first parameter - the tricky bit:

Set the value of parameter number 0 (the first one) to be the same as the value of the variable containing the file name.

You should now be able to right-click on your package to execute it, and produce the required results!

Right-click on the package in Solution Explorer to execute it.

Green ticks!

What you should see if everything works.

I'm loving Integration Services!