I'm creating an SSIS package to import a CSV file into a table. Currently I'm importing a flat file, creating a derived column, performing a data conversion, and then loading that to the DB.

Package Data Flow

The issue I'm encountering is while trying to convert a column of type DT_DBTIMESTAMP2 to a datetime2 its throwing the following error:

[OLE DB Destination [78]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".

the format form my data is:

20-NOV-18 06.09.43.928460000 PM

In my derived column im using the following expression which appears to be giving me the result im looking for:

"20" + SUBSTRING(DSTAMP,8,2) + "-" + 
    (SUBSTRING(DSTAMP,4,3) == "JAN" ? "01" : 
     SUBSTRING(DSTAMP,4,3) == "FEB" ? "02" : 
     SUBSTRING(DSTAMP,4,3) == "MAR" ? "03" : 
     SUBSTRING(DSTAMP,4,3) == "APR" ? "04" : 
     SUBSTRING(DSTAMP,4,3) == "MAY" ? "05" : 
     SUBSTRING(DSTAMP,4,3) == "JUN" ? "06" : 
     SUBSTRING(DSTAMP,4,3) == "JUL" ? "07" : 
     SUBSTRING(DSTAMP,4,3) == "AUG" ? "08" : 
     SUBSTRING(DSTAMP,4,3) == "SEP" ? "09" : 
     SUBSTRING(DSTAMP,4,3) == "OCT" ? "10" : 
     SUBSTRING(DSTAMP,4,3) == "NOV" ? "11" : 
     SUBSTRING(DSTAMP,4,3) == "DEC" ? "12" : " ") 
+ "-" + LEFT(DSTAMP,2) + " " + SUBSTRING(DSTAMP,11,2) + ":" +
SUBSTRING(DSTAMP,14,2) + ":" + SUBSTRING(DSTAMP,17,2) + "." +
SUBSTRING(DSTAMP,20,7)

I set up a data viewer and it appears to be giving me the desired result:

Data Viewer Output

I've browsed numerous posts looking for an answer haven't found one that works for my situation. The package is making it way through to the OLE DB Destination before failing.

I've scrubbed the input file looking for anomalies and all rows are correct.

figured another set of eyes could help, any suggestions are greatly appreciated!