Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
CSV File having a Value, which is getting altered while loading in SQL Table

Proposed Answer CSV File having a Value, which is getting altered while loading in SQL Table

  • Tuesday, May 01, 2012 7:04 PM
     
     
    Sign In to Vote
    0

    Hi..

    I have DFT [Flat File Source To OLEDB Destination - No Other Transformation involved in it] in a SSIS Package which is receiving data from a CSV file and storing it in SQL Server (2008 R2) Table. I noticed recently that there was a column called 'ACTUAL' where in CSV file, for one row data value was 70.99999438 where as when it got uploaded in my SQL Table, value became: 70.6875.

    In SQL Table, that 'Actual' column is a Float Type Column. I also used Data Viewer to check what is coming in the Pipe before entering into Destination in the DFT. But I found the correct 70.99999438

    Even I used SQL Server import Export wizard to load that CSV, but then also, value is again becoming 70.6875

    Any suggestion please? What else I need to check and what could be the possible cause for this?

    Thanks... Regards, AM

    Regards, Avik M.

     

All Replies

  • Tuesday, May 01, 2012 7:08 PM
     
     Proposed Answer
    Sign In to Vote
    0
    FLOAT is not an exact datatype.  If you want to preserve your data you need to change your sql  column datatype to a Decimal with the appropriate precision.

    Chuck

    • Proposed As Answer by Aalam Rangi Wednesday, May 02, 2012 6:54 PM
    •  
     
  • Tuesday, May 01, 2012 7:22 PM
     
     
    Sign In to Vote
    0

    Thanks for your response. But sorry! that particular column is having values like 15 or 34.2 or 75.33459 or 12.22 etc etc... i.e. the precision is not fixed here for the available data. Also I should not store value 70 as 70.00000 etc.

    What else could be the reason of the issue and what are other solutions for this please?

    Regards, Avik M.

     
  • Wednesday, May 02, 2012 5:31 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    1

    Sorry, your interpretation of precision is not correct Avik.  Nor is your assumption that "70" can't be stored as "70.00000".

    Chuck is completely correct - FLOAT data types are NOT precise, and changes to the values between various repositories are to be expected.  Floating point numbers are binary representations of a decimal number consisting of a significand and base-10 exponent.  They are binary approximations of a decimal number intended to store a certain number of significant digits regardless of how large or small the number is (the exponent is stored separately).  FLOAT types store seven digits of significance, which means SQL is attempting to store "70.99999", and happens to round that off (in a binary fashion).  If you wish to preserve more significant digits, you'll first have to determine the maximum number of decimal places you want to store, and use a REAL, NUMERIC or DECIMAL data type in SQL to store them.  (Equivalent to SSIS's DT_R8, DT_NUMERIC, or DT_DECIMAL).

    Secondly, how "70" is actually stored in SQL Server is irrelevant.  If you don't want to see trailing zeroes, then when you retrieve values from SQL Server, you need to format them appropriately.  If you kept your column as a FLOAT, SQL Server and the system that retrieves data from it are equally likely to return a value formatted like this "7.533459e-01" as they are to return "75.33459".  Exponential notation is a typical format for floating point values.

    Two key points - floats are approximations, and format isn't the same as value.

    Todd McDermid's Blog Talk to me now on


     
  • Wednesday, May 02, 2012 5:49 PM
     
     
    Sign In to Vote
    0

    Hi,

    Have you checked for triggers in your table?

    Are you sure your table field is not a calculated field?

    FLOAT stores number with 14 or 15 significant digits, so 70.99999438 should be stored "as is", in the worst case it should

    return 70.999994380001 or 70.999994379999, not 70.6875! That's a 0.5% error, far too much for a 14 digits FLOAT variable.

    In fact, I find quite suspicious that having 14 or 15 digits to be assigned, your value is rounded to such a precise value : 70.6875 (70+11/16)

    What is your process about?

    Is it converting metric (cm) to inches or something like that?

    Your 70.6875 (70+11/16) looks like a measurement in imperial units to me.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu


     
  • Wednesday, May 02, 2012 6:26 PM
    Moderator
     
     
    Sign In to Vote
    0
    FLOATs can store a maximum of SEVEN digits of the significand, not 14 or 15.  REAL data types store up to 15 digits.

    Todd McDermid's Blog Talk to me now on

     
  • Wednesday, May 02, 2012 6:39 PM
     
     
    Sign In to Vote
    0
    Even with 7 digits, you shouldn't expect a 0.5% error by the mere fact of loading a constant into a field.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

     
  • Wednesday, May 02, 2012 9:17 PM
    Moderator
     
     
    Sign In to Vote
    0

    I'm skeptical of that report.

    Todd McDermid's Blog Talk to me now on