Including the Date in a Filename in SSIS using an Expression

Category : Blogs Published : October 1, 2012 User Rating : 4.5 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 : https://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 by MZ on Fri 25 Aug 2017 14:25. Report Inappropriate Post

Hi Garth, just experienced the same issue. Adding additional backslashes solved for me. Since you are using IP in your path to the server unsure if you'd need 4 or 2 to start?
"\\\\server\\pathname1\\pathname2\\exportfile" + (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) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss",GetDate()),2) +".txt"
Post by Dennis Schall on Wed 06 Sep 2017 21:49. Report Inappropriate Post

this is great and I got it to work. BUt I need to acually use the date stamp on the file in renaming the file. How would I do that.
Post by ammz on Fri 27 Oct 2017 11:24. Report Inappropriate Post

expression is working for me.But my file name gets generated with an index value.Like if I am running the package for the fifth time , the file name getting generated is as like : Sample (005)
Kindly help me in resolving this issue.
Post by Lucas on Fri 29 Dec 2017 16:26. Report Inappropriate Post

Muy bueno el articulo, quisera consultar una duda.
En mi caso el nombre del archivo que tengo que evaluar es de búsqueda y no de salida.

Llego hasta este punto :
"\\\\gr06\\SFTP-BC\\Resultados_BC\\CMOPEDI_ALTA_Pr_ASCREDIAL_"+(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" , DATEADD ("dd", - 1, GetDate ()) ), 2) +"_"+".txt"

Generando este nombre :

\\gr06\SFTP-BC\Resultados_BC\CMOPEDI_ALTA_Pr_ASCREDIAL_20171228_.txt

Los archivos que debo ir a buscar tienen el horario en el que se generar pero como obviamente estos pueden variar.
¿Hay alguna especie de comodín que me permita interpretar el file mas aya de que la hora varié?

Gracias,saludos.



Post by Omar on Wed 15 Aug 2018 09:01. Report Inappropriate Post

Excellent post! Clear and to the point. Thank you.
Post by Savin on Mon 19 Nov 2018 18:06. Report Inappropriate Post

Hello,

Thanks for the post.
I'm trying to overwrite a file with a time stamp from multiple Data Flow Task. But When I use Expressions for the timestamps(hhmmss) for each Flat file destination manager, it is generating multiple files instead of one file. Any suggestions or alternatives will be more helpful for me.

Thanks.
Post by A R Gudipati on Mon 14 Jan 2019 12:32. Report Inappropriate Post
Website : http://www.cube-itsolutions.co.za
Try this
"c:\\Path\\name"+SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 ) + (MONTH(GetDate()) == 1 ? "Jan" : MONTH(GetDate()) == 2 ? "Feb" : MONTH(GetDate()) == 3 ? "Mar" :
MONTH(GetDate()) == 4 ? "Apr" : MONTH(GetDate()) == 5 ? "May" : MONTH(GetDate()) == 6 ? "Jun" :
MONTH(GetDate()) == 7 ? "Jul" : MONTH(GetDate()) == 8 ? "Aug" : MONTH(GetDate()) == 9 ? "Sep" :
MONTH(GetDate()) == 10 ? "Oct" : MONTH(GetDate()) == 11 ? "Nov" : MONTH(GetDate()) == 12 ? "Dec" :
"ERR") +SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 )+ ".csv"
Post by Deni on Mon 04 Feb 2019 14:15. Report Inappropriate Post

GREAT INFO thank you, I had tried many things to fix an issue and was able to find the fix here. Just needed to add additional backslash to my file path....THANK YOU
Post by Mike on Tue 16 Jul 2019 17:53. Report Inappropriate Post

Is there a way instead of using GETDATE() in the expression that could grab file date so if you had to run files later then the actual processing date you could still get the correct date on the filename when it is written out?
Post by Justin on Thu 11 Jun 2020 19:41. Report Inappropriate Post

My file is working to an excel destination and evaluates correctly in the expression but doesn't overwrite the name with the date. I've also tried excelfilepath instead of connection string with no luck, any ideas?
Post by MOHAMED BEN AMAR on Wed 01 Jul 2020 15:39. Report Inappropriate Post

To have the previous date :

"c: \\ Chemin \\ nom_"+


(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) +

( DAY(GETDATE()) == 1? RIGHT("0" + (DT_STR,4,1252 ) DATEPART( "mm" , DATEADD ("mm", - 1, GetDate ()) ), 2) : RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) ) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , DATEADD ("dd", - 1, GetDate ()) ), 2)


+".csv"
Post by siva on Wed 23 Sep 2020 08:44. Report Inappropriate Post

i have a file like this

volunteers_20200917(2020-09-23 034246)
but i want file like Volunteers_20200923

how to replace that file
Post by Richard F on Thu 04 Nov 2021 07:31. Report Inappropriate Post

I used this as destination location folder and file name which includes seconds and is a csv file
"\\\\xxxxxxxxxx-17\\folder\\Archived\\xxx_xxxxx_Responses_" +
(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) + "." +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss",GetDate()),2) + ".csv"
Post by Sandhya on Thu 23 Dec 2021 08:14. Report Inappropriate Post

I have a file name as 'file1_ddmmyyy'. Format any ideas how to write an expression for this format
Post by Chris Fugett on Wed 28 Dec 2022 21:22. Report Inappropriate Post

great, i have a project that reads in two files with the date expressions like shown here. My issue is, my dba does not like this stuff coded inside the package and wants it set up in a environmental variable. I cannot seem to get any kind of expressions set up in a environmental variable and get it to read right. Any suggestions or help is appreciated!
Post by preethi kalluri on Mon 07 Oct 2024 19:47. Report Inappropriate Post

How to get the same end time stamp for the multiple files in the SSIS master package?

Post a comment   No login required !

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