SSIS Data Conversion string to timestamp

2
votes

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
1
sql-serverssissql-server-data-tools
What is the initial type of the column ? What is your data source? – Amira Bedhiafi
The source was 2 flatfiles, and I joined them. This data conversion is the process after join. – yuliansen
Do you have any empty lines? – Amira Bedhiafi
Hi, To avoid that, I'm using conditional split. But it still not working. Do you have any idea? – yuliansen
Can you share a line of your input ? – Amira Bedhiafi

1 Answers

3
votes

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