Including the Date in a Filename in SSIS using an Expression

Category : Blogs Published : October 1, 2012 User Rating : 4 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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.

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/Including the Date in a Filename in SSIS using an Expression.aspx

Keywords

SQL,SSIS,Expression


Comments
Post by Norman Stanley Fletcher on Mon 10 Feb 2014 15:30. Report Inappropriate Post

"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("mm",GetDate()),2) + ".csv"

IS INCORRECT, IT SHOULD BE

"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"
Post by SQL Matters on Mon 10 Feb 2014 20:22. Report Inappropriate Post

Many thanks for taking the time to point out that error in the second script. I've changed the original article to correct it ("mm" changed to "mi").
Post by Alex Liao on Sun 16 Mar 2014 23:33. Report Inappropriate Post

Don't forget to add "\\" + to the path when saving to network.

Thanks!
Post by John on Mon 14 Jul 2014 18:56. Report Inappropriate Post

Great Post!
Post by Christoph on Wed 08 Oct 2014 10:12. Report Inappropriate Post

Nice and clear explanation.
Post by James Carter on Wed 21 Jan 2015 17:55. Report Inappropriate Post

Thanks solved an issue I had with dates
Post by Christopher D on Fri 13 Mar 2015 10:33. Report Inappropriate Post

This is a great post - thank you! . Just one question though - how do I append YESTERDAY'S date? I'm trying with DATEPART etc but not getting it right.....
Post by SQL Matters on Fri 13 Mar 2015 11:49. Report Inappropriate Post

Thanks for the question.

To use yesterday's date just use DATEADD to adjust the current date. In the expressions above if you replace GetDate() with DATEADD("dd",-1,GetDate())) that should work !

Post by Dave Rowland on Fri 05 Jun 2015 19:34. Report Inappropriate Post

Wonderful post..., much appreciated!!!
Post by crzyptl on Sun 07 Jun 2015 23:51. Report Inappropriate Post

I have multiple files with different extensions (txt, cvs, xls) I want to move those files and rename at the same time in one expression. Is that possible ?
Post by Poonam on Mon 14 Sep 2015 08:00. Report Inappropriate Post

Very nice post...

Don't forget to add "\\" +

"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"

then my code work...Very Nice...:)
Post by Ari on Mon 14 Sep 2015 14:40. Report Inappropriate Post

Thank you for all the information posted, but I ams still stuck on excluding weekends when getting yesterday's date. How do you go about this?
Post by Steve Sofar on Fri 08 Jan 2016 15:23. Report Inappropriate Post

Thank you very much for your post
you saved my day !!
very much appreciated
Post by andriy k on Thu 04 Feb 2016 18:19. Report Inappropriate Post

thanks!
Post by Chris on Fri 04 Mar 2016 11:10. Report Inappropriate Post

great post

you also mention file names

Output25Dec2012.csv
Output_25Dec2012_2345.txt

I am trying to put the month as Dec, or Mar etc. in the file name like above- how would I construct this?
Post by viral on Tue 15 Mar 2016 18:21. Report Inappropriate Post

I have same issue as Chris , but i want to convert Month name in to month number...any help would be appreciated.
Post by Saad on Fri 01 Apr 2016 04:45. Report Inappropriate Post

I want to get the day in single digit for the first 9 days and then 2 digits from 10th onwards. I am using the following date string in expression.
Right("0" + (DT_STR,2,1252) DatePart("dd",getdate()),2)
Is there anything I can modify to the string to manage my requirement.
Post by Brian Behm on Fri 22 Apr 2016 22:18. Report Inappropriate Post

Great post!

If I was to pass in an OutputFolder parameter such as "\\SomeServer\Exports" as the place I want the file to be exported, how can I "programmaticly" be sure that the trailing backslash is appended to the resulting file path. I realize that I could just say, "Make sure you add it to the parameter" but I'd like to figure out how to append a "\" if the person entering the parameter forgets to add it.

Thanks!
Post by preethy on Fri 03 Jun 2016 08:07. Report Inappropriate Post

Thank you very much. just sorted out my issue. very helpful :)
Post by SQLCHANTER on Fri 15 Jul 2016 01:16. Report Inappropriate Post

Hi . this is nice post. very useful .
How to use XML file output so that we get a neat looking html report , to see on a SSRs Server ??
Post by Gutta Chowdary on Wed 28 Sep 2016 22:50. Report Inappropriate Post

Thank You Very Much. Its clean and Nice Artical.
Post by east on Tue 11 Oct 2016 00:27. Report Inappropriate Post

I need your guys help for the my SSIS exp:

For example: today 10/10/2016 (the data which I pull was from 10012016 to 10072016),

I would like the output file format as filename_1001_1007.txt which means last week data.

mmdd_mmdd

How can I get the exp. work? please advise, Many thanks.
Post by Gladys on Wed 25 Jan 2017 13:01. Report Inappropriate Post

Thanks for the Post.

How can I get the file name as the name with date of data range . My query is taking data of past 1 week (ie from 8-15 Jan if I am execute on 22) how can i get a name with that date and so on.

Post by Aniki on Wed 29 Mar 2017 13:08. Report Inappropriate Post

I want to ftp the flat files with different dates on one package and run package at same time, how do I include that in the package.
Aniki
Post by abhishek on Tue 09 May 2017 08:59. Report Inappropriate Post

Thank you so much. Very much appreciated.
Post by sangerie on Tue 25 Jul 2017 00:55. Report Inappropriate Post

Hi, I used the expression above. thanks!

But I have a question.

I save this file name in backup folder "sample_20170725072614.csv"
but when i need to save it in my database the file name is already like this "sample_20170725072615.csv"

How can i get the "sample_20170725072614.csv" to save this in my database?

Thank you.
Post by SQL Matters on Tue 25 Jul 2017 09:51. Report Inappropriate Post

It looks like you are recalculating the same expression a minute later, so are getting a slightly different timestamp. Could you change the logic so that the expression value gets stored in a variable, and then use the variable for both the filename and database value ? That will mean that the filename and database value are always the same.
Post by Garth on Tue 15 Aug 2017 22:16. Report Inappropriate Post

Hi, I must be close to getting this to output a network flat file with a date suffix, but getting twisted on the "\\" syntax. Hoping to get file named

SkillGroup_08152017.txt

using the following expression but it is complaining of escape character "\T" which I think is TFTP

"\\172.34.5.132\TFTP-Root2\cb\prod\cisco\SkillGroup_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".txt"

that path works in connection manager but not as an expression. any thoughts

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered