SSIS Date Part Expressions

If you are diving deep into SSIS you're probably familiar with the fancy string manip needed to get your file names and folder paths to line up with various date structures and formats.

Here's a few I've used recently that might come in handy. Feel free to offer improvments and supplements.

Month Date Short Year

@[User::Path]+@[User::FlatFileName] 
+ Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) 
+ Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) 
+ RIGHT((DT_STR,4,1252)YEAR(GETDATE()),2) 
+ ".txt"

Returns – \\UserPath\FlatFileName021013.txt

Year\Month\Day

@[User::ArchivePath] + "\\" 
+ (DT_WSTR, 4)YEAR(GETDATE()) 
+ "\\" + Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) 
+ "\\" + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)

Returns – \\ArchivePath\2013\02\10

Year-Month-Day

"\\\\drive\\path\\" 
+ SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 10) 
+ "_" + @[User::SourceFile]

Returns – \\drive\path\2013-02-10_File.xls

Year Month Day Simple

"PrefixFile" 
+ REPLACE(SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 10), "-", "") 
+ ".txt"

Returns – PrefixFile20130210.txt

Year Week Number

"FilePrefix_" 
+ (DT_WSTR,4)YEAR(GETDATE()) 
+ (DATEPART("week", GETDATE()) > 9 ? (DT_WSTR,2)DATEPART("week", GETDATE()) : "0" 
+ (DT_WSTR,2)DATEPART("week", GETDATE())) 
+ ".txt"

Returns – FilePrefix_201306.txt

Sime Date with Timestamp

Substring((DT_WSTR, 30) (DT_DBTIMESTAMP) GetDate(), 1, 16)

Returns – 2032-02-10 10:15

Leave a Comment

Your email address will not be published. Required fields are marked *