Dynamic File Name

In a recent project we needed to create an SSIS package to export data from a SQL Server table to a CSV file. One of the requirements was to dynamically append the date to the file name each time a file was generated. This post will cover how to create a dynamic file name with the date included (YYYYMMDD format) in the file name.

You should already have created the output file in the connection managers. If you haven’t done that—complete that first.

How to create a dynamic file name

When it comes to creating a dynamic file name, the expression for the CSV must be updated. Right click the CSV in Connection Manager and click on Properties. This will open a section on the right hand side which contains an expressions row.

Right click the CSV in Connection Manager and click on Properties.

Clicking on the ellipses will open a section where you can create a new property connection.

The statement below can be copied and pasted into the expression box to create a path:

“\\\\Servername\\SHARE\\FileName_”+(DT_STR,4,1252)DATEPART( “yyyy” , getdate() ) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mm” , getdate() ), 2) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “dd” , getdate() ), 2) +”.csv”

Note that the highlighted section will need to be updated to your environment—where are you putting the file created. The extra forward slashes in the file path are used to escape the other forward slashes for the file path. Adding one forward slash in the beginning of each forward slash (including one each for the two at the beginning of the file path) will ensure that your file path string gets interpreted correctly. The result will place a file name “FileName_YYYYMMDD” inside a folder called SHARE.

You might also move around parts of the expression if you want a different format.