SSIS Implicit Conversion from Float to Varchar does not Mimic SQL Server Functionality


  • This is easy to reproduce.

    Create an OLE DB Source with this query: SELECT CONVERT(FLOAT,0.0648) DPFLOAT

    Create an OLE DB Destination and map the FLOAT value to a table with a VARCHAR(15)

    The mapping will show type RT8 mapped to DT_STR(15)

    Run the package and check the result.  The Value will be 6.47999999!

    SSIS is not correctly handling the implicit conversion. (It's truncating the exponential notation)

    SQL Server 2008 version.

    Friday, July 19, 2013 6:57 PM

All replies

  • In case this wasn't clear:

    0.0648 <> 6.47999999

    They are off by two orders of magnitude - 100X !


    Friday, July 19, 2013 7:01 PM
  • This has to be a bug.

    Incoming OLE DB source has a float column with a value of 0.0648

    Mapping this to a OLE DB Destination column of varchar(15) results in a value of 6.48, off by a factor of 100 !!!

    The mapping in the OLE DB Destination show RT8 mapping to DT_STR(15)

    While this is easy to reproduce in SSIS, you will not be able to reproduce this in SQL Server with these values.

    It can be reproduced at much bigger extremes such as this:

    declare @float as float = .0000123456789

    select @float, left ( @float, 7 )

    => 1.23456789E-05, 1.23457

     Notice how small this number is compared to the value above.

    SSIS is not handling this implicit conversion correctly.  This should map to the original value in this case.  A varchar(15) is large enough to receive this number in scientific notation.  SSIS should mimic what SQL Server does in this instance.

    Monday, July 22, 2013 11:36 PM