Including the Date in a Filename in SSIS using an Expression

[Originally posted by]: http://www.sqlmatters.com/Articles/Including%20the%20Date%20in%20a%20Filename%20in%20SSIS%20using%20an%20Expression.aspx

When creating a file in SSIS it can be useful to incorporate a date and/or a time stamp into the name of the file, for instance :

   File_20121225.csv
   File25122012_1339.txt
   Output25Dec2012.csv
   Output_25Dec2012_2345.txt

Fortunately it’s fairly easy to achieve this using expressions. Expressions are a way of calculating a value based on various criteria. In this case I’ll use an expression to generate the file name for a file connection, based on the current date with the format YYYYMMDD.

To illustrate this I’ve created a very simple SSIS package that runs a SQL query (in my case this calculates the size of each database) and exports the results to a file. The package has a single Data Flow Task as follows :

Including the Date in a Filename in SSIS using an Expression image 1

Switching to the Data Flow tab you can see from the screenshot below that the task has an OLE DB Source which uses a SQL Server connection, and a Flat File Destination which uses a File Connection :

Including the Date in a Filename in SSIS using an Expression image 2

If I click on the Flat File Connection Manager (circled below) then the Properties window at the bottom right of the screen will show properties information for the connection :

Including the Date in a Filename in SSIS using an Expression image 3

The initial setting for the ConnectionString property can be seen in the screenshot above in the “Properties” window, but this can be customised using the Expressions property a bit lower down (circled above). Click on the 3 dots to the right and the “Property Expressions Editor” window will be displayed. Click on the Property column and a dropdown will display all the properties that can be customised using an expression. Select ConnectionString and then enter the expression directly into the Expression column on the right :

Property Expression Editor
The expression that I’ve used here is :

“C:\\DatabaseSizes_” + (DT_WSTR,4)DATEPART(“yyyy”,GetDate()) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mm”,GetDate()) ,2) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“dd”,GetDate()),2) + “.csv”

This will generate a file name like : C:\DatabaseSizes_20120928.csv.

If you need to include the time in the filename as well then here is an alternative expression which adds the time in the format “hhmm” :

“C:\\DatabaseSizes_” + (DT_WSTR,4)DATEPART(“yyyy”,GetDate()) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mm”,GetDate()) ,2) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“dd”,GetDate()),2) + “_” +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“hh”,GetDate()),2)+
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mi”,GetDate()),2) + “.csv”

This will generate a file name like : C:\DatabaseSizes_20120928_0913.csv.

Visual Studio also includes an Expression Builder which makes it easier to create and validate expressions. Click on the 3 dots to the right of the expression to display the “Expression Builder” window :
Expression Builder
You can enter the expression into the window or build it up using the various functions and variables. If you click the “Evaluate Expression” button it checks for any errors and determines the value of the expression – a very useful feature.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s