SSIS - Casting DT_STR to DT_DECIMAL returns incorrect values

Problem:

I am using SSIS on VS 2015, and I make a test using OLE DB Source and OLE DB Destination (tables on SQL Server 2017). The problem is the Source table I have the one field varchar(10) type and the values is decimal´s (remember that this field is varchar(10).

The values TB_ are:

On Integration Services, I used OLE DB Source, Data Conversion and OLE DB Destination.

On Data Conversion Component, I set the type to numeric (and decimal I tried after):

On OLE DB Destination, I mapped the fields:

But, when I execute the Package, The OLE DB Destination have the values:

The correct values on CAMPO2 field will be 4.51, 20.5 and 98.54

The struct table OLE DB Destination is:

Any Suggestions?

Solution:

Problem cause

The main cause is the decimal seperator, the varchar column contains a comma , instead of a point .

Experiments

If we try to case the following text to decimal "1,2" the result is:

12.00

If we try to case the following text to decimal "1.2" the result is:

1.20

Solution

Instead of using Data Conversion transformation, try using a Derived column with the following expression:

(DT_DECIMAL,2)REPLACE([CAMPO2],",",".") 

Original post: https://stackoverflow.com/questions/57101113/siss-problems-to-convert-dt-str-to-dt-decimal-decimals-going-to-integer-part/57101868#57101868