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

3
votes

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!

2
sql-serverdatetimessisetlscript-task
The problem was fixed, thank you so much - Võ Thái Phúc

2 Answers

1
votes

Did you try to redirect the error output to a flat file and check if the rows contains wrong data in the datetime field.If you cant find any problem try to put the string in a valid format by inserting "/"," " and ":" for example your string "20190523100520" will become "2019/05/23 10:05:20"

1
votes

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.