This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Creating the loop over files
Adding the SQL Task

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.

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:

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:

Choose to loop over files (it's the default, so you won't need to change anything here).
Click on the Browse... button to choose the folder containing your files.
Choose which files you want to loop over (here we've chosen all files with extension .log).
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.

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:

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:

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:

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!

Some other pages relevant to the above blog include:

This blog has 1 thread

Add a new post

From:	QueenB
When:	05 Apr 18 at 01:16

Thank you for this. Exactly what I needed in a hurry!!!

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Writing file names to a SQL Server table in SSIS

Creating the loop over files

Adding the SQL Task

Head office

London

Manchester