Monthly Archives: April 2013

SSIS: Adding timestamp into filename

Once and a while you need to “post process” your source files for example moving them into archive directory etc. In such directory you might then end up having dozens of files so you need to rename them also. The most common way to achieve a robust and clear formula of renaming files is to add a timestamp inside the filename.

In SSIS (SQL Server Integration Services) there is no sophisticated way (read: nice string/date function) to do this but leveraging expressions and string manipulating functions you can have a nice one like the one I’ve used:

(DT_WSTR, 4) (DATEPART( "yyyy", @[System::StartTime]  )) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "mm", @[System::StartTime]  )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "dd", @[System::StartTime]  )), 2) +
"_" +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "Hh", @[System::StartTime]  )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "mi", @[System::StartTime]  )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "ss", @[System::StartTime]  )), 2)

Now, just add this expression as a pre- or postfix to your processed file and you end up with a nice and clean result like: sourcefile_20130412_082024.csv