SSIS Data Conversion string to timestamp

user_7948134 0
Viewed 610

I'm trying to convert a column into timestamp.

Value sample : ETime : 2020-06-04 17:46:53.750

What I've done

  1. I add the data conversion, edit the input column (ETime)
  2. Input and output properties on Show Advanced Editor
  3. Data conversion output, choose copy of ETime
  4. Change : FastParse : True and DataType : database timestamp [DT_DBTIMESTAMP]

But it returning this error

Error: 0xC02020C5 at Data Flow Task, Data Conversion [11]: Data conversion failed while converting column "ETime" (94) to column "Copy of ETime" (15). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Am I missing something?

edit

Data sample 1.

id, cid, invid, etc
33333,44444,abc333334,shoes
33334,77777,abc3333347,shoes
33335,88888,abc3333358,book

Data sample 2.

cid,invid,bcode,ETime
44444,abc333334,03,2021-01-03 20:08:51.200
77777,abc3333347,03,2021-01-06 17:30:53.210
88888,abc3333358,03,2021-01-04 20:04:22.600

I'm joining them on cid It is possible for Data sample 2 have broken data as

cid,invid,bcode,ETime
44444,abc333334,NULL,Nodatafound
sql-server-data-tools ssis sql-server
1 Answers

Tried with a missing row and got the exception:

Error: 0xC02020C5 at Data Flow Task, Data Conversion 1: Data conversion failed while converting column "Column 0" (25) to column "Copy of Column 0" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

enter image description here

Now tried without any missing row and its worked enter image description here

It seems there are missing value available or data not in correct format. Try ignore failue from Configure Error Output of data convertion component. enter image description here

user_54964980
Related