SSIS Express Column Output

Question

Reading from a flat file, in which there is a column containing the year and month, in the following format → "201212".

I need to insert this into the DATETIME2 column, but first I have to parse it with "/" and add "01" as the day. So what 201212 becomes 2012/12/01

My expression in my derived column transformation is as follows:

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

It seems that it should work, and SSIS accepts this (as it can parse the expression in it), but when it starts the package, it generates an absolutely useless error "An error occurred while trying to perform the titration." along with the column on which the error was.

I did not create this package, it was laid on me, and I was told to make it work.

Thanks in advance to the interwebs family.

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]