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 :
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 :
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 :
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 :
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 :
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.