SSIS file and date expressions

While others have undoubtedly blogged about expressions, this post will cover the ones that I seem to be dealing with most frequently.  What is not covered is yyyymmdd or ccyymmdd to a valid date format ever since I discovered the fast parse option (TODO:  cite)

 

One thing I have found is that given the lack of real estate in the expression editor, it is helpful to have lots of variables doing small tasks vs writing the one big epxression.  For example, the expression to rewrite a file name with a datestamp in it, has 4 variables:  base file name, extension, date stamp and the rewritten file name

 

Expression to strip the file extension from a file name

SUBSTRING(@[User::CurrentFile],1, (LEN(@[User::CurrentFile]) -FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)))

 

Expression to get the file extension from a file name

SUBSTRING(@[User::CurrentFile], (1 + LEN(@[User::CurrentFile]) - FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)),len(@[User::CurrentFile]))

 

Date string (yyyymmdd)

(DT_WSTR, 4) year(@[System::StartTime]) + right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2)

Datetime string (yyyymmddhhmmss)

(DT_WSTR, 4)  year(@[System::StartTime]) +right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2) + "TODO:  time portion"

Insert a date string before the final period

SUBSTRING(@[User::CurrentFile],1, (LEN(@[User::CurrentFile]) -FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1))) + "." + (DT_WSTR, 4) year(@[System::StartTime]) + right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2) + SUBSTRING(@[User::CurrentFile], (1 + LEN(@[User::CurrentFile]) - FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)),len(@[User::CurrentFile]))