SSIS: Executing SQL statement for each file
How to store the name of each file in a folder in a SQL Server table using an Integration Services package.

Posted by Andy Brown on 05 April 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

List of web log files

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

 

I found this article on the subject useful also.

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:

foreach loop container task

Drag this task onto your package.

 

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

Editing the foreach task

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:

Collection tab

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:

Steps to create for loop

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:

Creating new variable

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

 

Here I've called my variable FileName:

Variable to hold file name

The variable will be in the User namespace.

The index number of 0 refers to the column number returned from the results set. Because we're only getting back one thing on each pass through the loop - the file name - there's only one column number, and because things are numbered from 0 in SSIS, this has index number 0!

Adding the SQL Task

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

Table to hold 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:

Execute SQL Task

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

 

Here's what you should now see:

The SQL task

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:

SQL Statement to execute

The SQL statement to execute.

Here's what this SQL statement looks like:

INSERT INTO tblFile

(FileName)

VALUES

(?)

Note that the question mark represents the first - and only - parameter value to be passed into the SQL statement.

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

The parameter mapping

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!

Executing a package

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

 

Green ticks!

Green ticks showing success

What you should see if everything works.

 

I'm loving Integration Services!

This blog has 0 threads Add post