BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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!