SSIS Derived Column Expression

Question

Reading from a flat file that has a column containing the year and month in the following format --> "201212".

I need to insert this into a DATETIME2 column but first I must parse it with "/" and add a "01" as the day. Such that 201212 would become 2012/12/01

My expression in my Derived Column Transformation looks like this:

(DT_DBTIMESTAMP2,0)((DT_WSTR,4)SUBSTRING(RptMthDTM,1,4) + "/" + (DT_WSTR,2)SUBSTRING(RptMthDTM,5,2) + "/" + "01")

This seems like it should work and SSIS accepts this(as in it can parse the expression) but when running the package it throws a completely useless error "An error occurred while attempting to perform a type cast." along with the column it had the error on.

I didn't build this package, it was pawned off on me and I was told to get it working.

Thanks in advance interwebs family.

Tunesmith

Is 201212 accurate? Or should that have been 20121212 (YYYYMMDD)? – Yamauchi

ya 201212 is accurate. Why they just want to store the month without the day I have no idea – Tunesmith

Answer 1

DT_DBTIMESTAMP2 can only be converted from a specific string format: yyyy-mm-dd hh:mm:ss[.fffffff]

You can use this expression instead:

(DT_DBTIMESTAMP2,0)((DT_WSTR,4)SUBSTRING(RptMthDTM,1,4) + "-" + (DT_WSTR,2)SUBSTRING(RptMthDTM,5,2) + "-" + "01 00:00:00")

More details: [http://msdn.microsoft.com/en-us/library/ms141036.aspx][1]

Brey

Thank you sir. I did check to make sure I could convert between the two but it would never have occured to me that it would fail because i chose to use "/" instead of "-". – Tunesmith