Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
In this blog
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
(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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.