Search code examples
ssisssis-2012ssis-2008

SSIS 2008 - Float value exported to exponential value in Flat file destination

I am trying to export the table to flat file destination. Float column value (0.0911780821917808) is exported into 9.1178082191780821E-2 in flat file.

Table create table Test ( col1 float )

Col1 0.0911780821917808

Exporting this table to flat file destination. However exported to value "9.1178082191780821E-2" in .txt file. However correct value is coming in the DataViewer after OLE DB Source.

Please guide to export the value as it is to flat file.

Advance thanks for all your time

Regards,

Stalin

Solution

  • I had similar issues before and what proved to be the safest way is by converting to string at source via STR function. For example you can read your table in the OLEDB Source with a SQL query like this:

    SELECT LTRIM(STR(col1,25,18) ) Col1casted
FROM
TEST

    Then you may need to recreate the flat file destination (or change the data types)

    In this example I'm setting the converted value to a 25 long of which 18 are decimals but you can adjusted to make sure it covers the range of values in your table.

    For Oracle:

    SELECT TO_CHAR(0.0911780821917808267712341,'9D9999999999999999') FROM DUAL;

    above the format is set to 16 decimals, example in sql fiddle

    https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=487fe7bf6cf3a2264f988523e5ac7a3d