SSIS ERROR "Conversion failed because the data value overflowed the specified type"

Question

I'm using ssis to parse file txt into sql server. I use script task to convert a string example "20190523100520" (type :yyyy-MM-dd HH:mm:ss) to datetime (it's data type in table sql ).

But I get the error:

[OLE DB Destination [68]] Error: "There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[NewEffectiveTS_NXX_X] on

OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "Conversion failed because the data value overflowed the specified type.

In script task, tag inputs and outputs, i use database timestamp [DT_DBTIMESTAMP] type for Columns [NewEffectiveTS_NXX_X]

Everyone help me. Thank you so much!

Answer 1

To convert this value to datetime datatype inside the script you can use DateTime.ParseExact() function:

DateTime.ParseExact([Column],"yyyyMMddHHmmss",System.Globalization.CultureInfo.InvariantCulture)

Because the string value 20190523100520 cannot be implicitly converted to datetime by mapping a column of type string to another of type datetime.