Wednesday, February 10, 2016

Add date stamp to SSIS export files

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, like this:



1) Select the connection manager:


2) Go to properties, Expressions, click on  the dotted button



3) In the popped up screen, choose ConnectionString:


4) Put the following expression in the Expression Builder:


"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (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"

Every backslash in the path needs to be typed twice so \\sftp.xxx.nl\xxx\csv\ becomes  \\\\sftp.xxx.nl\\xx\\csv\\ 

5) Save and run

6) When you also want the time use this:

"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (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"