SQL Server Integration Services: Setting Input Filenames Dynamically from a SQL Server Table

Being a recent convert from SQL Server 2000, I was used to the DTS framework for importing files to the database. From recent blog posts, you will see that it's been a learning curve in getting completely up to speed with SQL Server 2005's Integration Services.

When loading files into a database, I obviously found it important to dynamically set the file path so that values were not hard coded, otherwise it became a maintenance nightmare. Especially when promoting the packages to different environments.

I decided that for my application, the best way to dynamically set these properties was by creating a database table with the details on each file which was to be imported. Then I would extract the filename into the DTSX at runtime and use that to import the file. This way, the filenames would be picked up according to whatever environment the DTSX is running in, and there would be no hard-coded filenames to deal with.

So I thought I'd share a quick description of how I went about it, as much as a reminder for myself in future as for others who might stumble across this blog.

The table which contains the import file details is set up as follows:

CREATE TABLE [Import_File_Specifications](
[ImportFile] [nvarchar] (100) NOT NULL ,
[FileName] [nvarchar] (255) NULL ,
[FileExtension] [nvarchar] (30) NULL ,
[Location] [nvarchar] (255) NULL ,
CONSTRAINT [PK_Import_File_Specifications] PRIMARY KEY CLUSTERED ( [ImportFile] )
ON [PRIMARY] )
ON [PRIMARY]
GO

So I loaded the file specifications into there, and at runtime the DTSX would run some SQL against the table to build the filename based on the folder location, filename and file extension.

All I did was (this is assuming you're importing a flat file - tab delimited, csv etc):

1. Create a Package Variable to contain the filename of type String (right click on the Control Flow canvas and click 'Variables') .

2. Create an Execute SQL Task at the beginning of the DTSX package which flows into the rest of the process with an 'on success' connector.

3. On the General tab of the SQLStatement setting in the SQL Task, I put:

select Location + FileName + FileExtension as FileName from Import_File_Specifications where ImportFile = 'xxxx'"

'xxxx' is the unique ImportFile key for the file you want to use). Set ResultSet equal to 'Single Row' and SQLSourceType to 'Direct Input'.

4. The next step is to set the Package Variable equal to the filename which comes from the SQL Task. So on the Result Set of the SQL Task, press Add and select Result Name to equal FileName (from the SQL Statement you just entered), and select the Package Variable you created at the beginning.

5. Now, when the DTSX runs the SQL Task, it should set the Package Variable to equal the filename which comes from the database. The final step is to set the ConnectionString of the file's connection manager equal to the Package Variable which contains the filename. Select the connection manager, and on its properties which appear on the right hand side, click the '...' button which appears when you click the 'Expressions' field.

6. When the Property Expressions Editor appears, Press the '...' button to add a new expression property and select 'ConnectionString' from the drop down on the left. Then choose your package variable from the drop down on the right hand side.

That's it! When you test your DTSX package, if you've set up everything correctly then the SQL Task should retrieve the filename from the database, set the Package Variable equal to it and pass that into the Connection Manager.

For an Excel file, you set the 'ExcelFilePath' at runtime rather than the 'ConnectionString' property because the ConnectionString tells the package to use an OLE connection to the file. Note that you must set your PackageVariable to have a default value otherwise the package will not compile.

I admit that this post could have done with more detail, and probably some screen shots but as I said, it's just really a note to remind me of this technique in future. Others have also found different ways of doing this which they might argue are better, but the way I have this set up it works for my application and will allow for different input filenames depending on whether the app is running in DEV, UAT or Production.