560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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.
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!
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
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.
What you should see if everything works.
I'm loving Integration Services!
25 Aytoun Street