none
SSIS Excessive Scale/Precision Flatfile Destination

    Question

  • Summary

    Whenever I output real numbers from a source to a flat file destination, SSIS always outputs the some numbers with excessive scale and others with the proper scale for the same numbers each time. For example a number such as 36.4854 would be outputted as 36.4853999999997, while a number such as 10.9841 would be outputted as they are. Again, this would not be random. I tried using the ROUND function and casting to the DT_NUMERIC and DT_DECIMAL types in a Transformation task, but the results are exactly the same. 

     

    Source

     

    Type: ADO Source

     

    External column

    • DataType is DT_R8 and cannot be successfully changed. For instance, after changing it and clicking “OK”, it reverts back to DT_R8.

     

    Output column

    • DataType is DT_R8 and if the above is changed to DT_NUMERIC, it will cause an error when this is changed to DT_NUMERIC to match it.

     

    Transformation

     

    As stated above, several functions were used to change the data type, but the results remained the same. It is empty for this column for now.

     

    Destination

     

    Type: Flat File

     

    DataType: string[DT_STR] or DT_NUMERIC with scale of 6.

     

     

    Environment

    The version of my product is SSIS under SQL Server 2008 R2 using Visual Studio 2008 Version 9.0.30729.4462 QFE. The environment is Windows Server 2008 R2 Datacenter 64-bit.

    Thank you for your help in advance.

    Tuesday, November 20, 2012 6:14 PM

Answers

  • Derived Column Transformation with <Replace column> can't change type of the underlaying column, you have to add new column with new data type. You can do it using Derived Column Transformation with <add as new column> or use Data Conversion component.
    Thursday, November 22, 2012 12:09 AM

All replies

  • use the Derived Column Transformation before dumping the data to file with <replace column> setting and SSIS Expression

    (DT_NUMERIC, 10,4)([TargetColumn])

    or whatever precision and scale you desire.

    This must work if you also set the Flat File Connection of the destinatoin to this datatype.


    Arthur My Blog

    Tuesday, November 20, 2012 6:20 PM
    Moderator
  • ArthurZ, thanks for your reply. I already tried this, but it did not produce the correct results.


    Tuesday, November 20, 2012 6:29 PM
  • What do you see in the target. Can you post a picture?

    Arthur My Blog

    Wednesday, November 21, 2012 7:13 PM
    Moderator
  • What do you mean by target? The CSV file? If the CSV File, please see a sample below. As you can see some of the numbers have 4-digit precision and the others have "unlimited" precision.

    SSIS Precision Issue

    Wednesday, November 21, 2012 9:33 PM
  • Derived Column Transformation with <Replace column> can't change type of the underlaying column, you have to add new column with new data type. You can do it using Derived Column Transformation with <add as new column> or use Data Conversion component.
    Thursday, November 22, 2012 12:09 AM
  • What do you mean by target? The CSV file? If the CSV File, please see a sample below. As you can see some of the numbers have 4-digit precision and the others have "unlimited" precision.

    SSIS Precision Issue


    Very clearly, the data hitting the file is not with the proper precision. Put a Data Conversion block just before it and make the necessary conversion to NUMERIC(n,4)

    Arthur My Blog

    Thursday, November 22, 2012 9:18 PM
    Moderator
  • Thank you, Piotr and ArthurZ; using the Data Conversion component fixed the problem.
    Monday, November 26, 2012 4:21 PM