Search code examples
sql-serverssisdecimaletldata-conversion

SSIS Data conversion transformation

in SSIS I read a csv file with column format - for example 1.25 or 2.50. In the datatransformation task I transform into decimal dt_decimal scala 2. In the datatable the column has the format decimal(18,2).

The data will be stored with 125.00 or 25.00 instead of 1.25 and 2.50.

What do I have to adjust?

Solution

  • Possible Issue causes

    (1) Data type mismatch

    I think that a similar issue is caused by data type mismatch between source and destination, or data transformation output and Destination.

    (2) Numeric separators

    Another cause may be if the numeric values contains a comma , as formatting such as thousands commas 1,000,000 and decimal separator . like 1.02.

    Possible solutions

    (1) Specify data type in source

    To prevent this issue to be caused by data transformations and if your source data is formatted well. Then there is no need for Data Transformation. Inside the Flat File Connection Manager editor. GoTo Advanced Tab, Select the column that contains decimal and change its data type (try DT_NUMERIC and DT_DECIMAL) and precision and scale property.

    enter image description here

    If the issue still happening, be sure that both input and output has same metadata (precision and scale).

    (2) Derived Column

    Or you should use a Derived Column Transformation with a similar expression:

    (DT_NUMERIC,18,2)[COLUMN]

    (3) Replace Separator using derived column

    You can replace separator using a derived column

    (DT_NUMERIC,18,2)REPLACE([COLUMN], ",", ".")