SSIS Parse Date

Originally published on 12/17/12

This past week I was working on outputting files, using SSIS, to a directory on the network. That's pretty standard, but because the client wanted to keep an archive of said files I wanted to append a date string to each one. So if the name of the file is emailupload.csv and it was exported on 11/17/2012 then I would name it emailupload121117.csv. Then it would not overwrite previous versions.

So the code in the variable screen of SSIS should look as follows:

 RIGHT((DT_WSTR,2)MONTH(@[System::StartTime]),2)
+RIGHT((DT_WSTR,2)DAY(@[System::StartTime]),2)
+RIGHT((DT_WSTR,4)YEAR(@[System::StartTime]),2)

It works great! Try it and feel free to ask questions in the comments.


Edit on 1/7/13

Obviously the formula above has the parts of the date in the wrong order for it to work properly. Using the code above, the file would be named emailupload111712.csv. That's not bad, but it wasn't what I was hoping to accomplish.

I also found out due to the beginning of the year that it doesn't use two digit months/days. For instance a file outputted today would have this as it's file name (using the corrected order of date parts): emailupload1317.csv.

That's not what we want at all. The code below should correct for both issues:

 RIGHT((DT_WSTR,4)YEAR(@[System::StartTime]),2)
+RIGHT("0"+(DT_WSTR,2)MONTH(@[System::StartTime]),2)
+RIGHT("0"+(DT_WSTR,2)DAY(@[System::StartTime]),2)

I'm curious to see how it works when the date is 1/10/13. Will it output the file as emailupload1301010.csv? Or correctly as emailupload130110.csv? I'll let you know if it doesn't work properly.