Using SSIS I had a problem passing a file name into a Data Flow Task as a parameter.
The Data Flow Task was part of a For Each Loop, which worked perfectly except for the Data Flow Task, which was hardcoded with the file name.
The solution came from http://aspnetcomplete.blogspot.co.uk/2012/08/ssis-package-using-flat-file-source-to.html (in case that link breaks in future).
How to import multiple CSV files into a table in SSIS
The article is specifically about importing multiple csv files into a database table, then moving the files to a new folder.
This article requires you to have the basic knowledge of how to use the IDE for SSIS packages.
Creating SSIS variables and directories
First we need to create our directories
- Create a folder c:\temp
- Create a folder c:\temp\data
- Create a folder c:\temp\data\Archived
Next we need create our variables and set their settings as the diagram below shows.
Build the SSIS workflow
Now on the Control flow place the following items and configure them as the screenshots below show.
- Foreach Loop Container
- Data Flow Task (Place inside the Foreach Loop Container)
- File System Task (Place inside the Foreach Loop Container)
Now double click on the Data Flow Task, this will take you to the data flow tab.
On the Data Flow place the following items.
- Flat File Source
- Ole DB Destination
Now configure the connections for the source file and destination
- Double click on the Flat File Source and create a new connection
- Give the connection a name and browse to a file in the “c:\temp\data” folder (in my case i have a CSV file)
- Set any other options you may require (in my case I changed the Header row delimiter to | as my CSV is pipe delimited).
- Select “Columns” and click “Ok”
In order to make the FlatFile connection dynamic configure the ConnectionString Property as an Expression.
varFilePath will update on each iteration of file in the Foreach Loop Container.
On the connection managers window you will see the connection you have just created.
Select this connection and in the properties pane select expressions and configure the expression as shown in the screenshot below.
- Now double click the OLE DB Destination and create a new connection.
- Select the data connection you want to use or create a new one
- Select your data access mode (mine is table or view)
- Select the name of the table or view or press new (pressing new will create a new table or view based on the data in your file)
- Select mappings on the left and map the columns from your file to your database table.
- Click “Ok”
Remember to link all your items together as seen below.
Now run your package and check the results.
What did we do?
We have just created a SSIS package that looks in a source directory for all files of extension CSV.
The package will iterate through each file and import the data in the file to a SQL table in our database, the package will then move the file to the archive directory.
How can I see my results?
You can run a select query on your table in your database and see if you have the expected data.
You can also check your c:\temp\data folder and you should notice the files are no longer there, they should now be in the c:\temp\data\Archived folder.
I hope this help you in your experience with SSIS.