Cannot convert between STR to DT-I8 in SSIS

Question

I'm having this problem when using data conversion the source column looks like this

[dti]
4.55
14.55
20.10

and my destination column is INT in SSMS, my output column from the source which is JSON file is STR 50. I tried DT-I4 and Float datatypes and still throwing an error, Is it because of the dot within the numbers?

[Data Conversion [2]] Error: Data conversion failed while converting column "dti" (75) to column "Copy of dti" (29). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

@billinkc Yes, I want to store it just the way I have it in the source, I need to do some aggregations on it, later on, that's why it's required — Big_Dwarf90

billinkc billinkc · Accepted Answer · 2020-10-08T05:24:13

The supplied values convert just fine to the number types I tried.

A more likely scenario is that you have non-numeric data in there.

Repro

Source query

SELECT *
FROM
(
   values 
        (CAST('4.55' AS varchar(50)))
, ('14.44')
, ('20.10')
)D(dti);

Data Conversion and the execution results both in the screenshot

I would configure the data conversion task to funnel my error rows to an alternate location so you can inspect the values that fail to convert.

Cannot convert between STR to DT-I8 in SSIS

1 Answers