Dynamic Filenames for FlatFiles with SSIS

Importing from flatfiles with SSIS is relatively quick and easy to setup. But what if you need to load from many files with the same structure but all with different filenames? You could do this by manually changing the properties of the connection, running the SSIS package to load one file and then repeating until all of the files had been loaded. It works, and I've done it myself when there have only been a handful of files to load into the database. Recently I was faced with a situation where there were in excess of 100 files which would all need to be loaded on an ongoiong basis. The manual approach wouldn't do the trick here - some automation was required.



The answer lay in using a dynamic filename for the source file. This is actually quite quick and easy to do once you know where to look. Here's what you need to do.


Bring up the properties on the connection to the flatfile and click on the plus sign to expand the list of expressions. Use the elipse button to bring up a window to allow you to add a new expression.

Select the ExcelFilePath option from the property dropdown list and then use the elipse button beside the expression column to build the expression that will return the value of the path and filename for the source file. In my case I concatenated together two package level variables I had created earlier.

You can use the Evaluate Expression button to check the value you get from the expression is what you expected to see.

From here you can build a data flow to load data (using this connection) just as you would using a hard coded connection.

This example has used an Excel flatfile. You can use a similar approach with text files. Rather than set an expression for the ExcelFilePath property, set the expression for the ConnectionString property.