SSIS - Getting extra digits in field after CSV import

Question

I have a very weird issue, and I'm wondering if anyone has seen this before. In my data flow task, which truncates a dozen tables and then repopulates them with the content of CSV files.

I have a field that I'm importing that's a float. In the CSV the value is 7692.31, but when I import it, the imported field is 7692.31005859375. I cannot see anywhere in that CSV the extra 005859375.

I've tried truncating the table manually, and reimporting the value and I can see that it's being reimported. I've also checked and none of the other flat file imports point to that table. Also, I've tried a derived column with just that amount, still get the decimals. I've tried to round it to 2 decimal places, I still get the 11 decimal places. But if I round it to 0, then I get no decimal places (as expected).

So, I'm kind of stumped. I mean, where are these values coming from? They're not the same for every line, some lines don't even have these decimal places. Why aren't they rounding? I thought that it might be coming from having too many imports at once, but when I removed all the others, I had the same problem. Could there be hidden characters in a tab delimited file?

Any help would be appreciated.

Eric

Answer 1

I had the same issue, using SSM the database-tasks-import flatfile - just a simple data import. Flatfile only has 2 decimal digits, but importing into float and got all these weird extra unprecise digits. Reading the other answers about IEEE 754 got me thinking that there was an extra data conversion I didn't want or need. So on the Preview Data screen, at the bottom, uncheck the "Use Rich Data Type Detection" and that fixed it for me. Still using float, all the same import data, but it all loaded into SQL exactly 2 digits as expected.

Answer 2

At some point when working with Float/decimal/numeric/date you need to also consider checking the regional settings regions handle data in different way. US may not format the date as your current region, in float some regions use a , the same way as a .

Answer 3

This is floating point error. Some terminating decimal fractions in base 10 become repeating fractions in base 2. For the most part, modern programming languages hide this or deal with it internally, but float and real data types in SQL do not.

From the documentation for float and real data types:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

If you want precise numbers, use numeric or decimal data types, which are precise.

---

Edit: Sorry, I was on my way out of the office, and ended up being unclear and incomplete in my haste. I actually meant to cancel my post!

To finish:

The problem here isn't necessarily with SQL Server data types, but how SSIS works with and converts to SQL Server data types. I'm betting you've got the data type in SSIS configured as DT_R8, since that's what a double precision floating point number is, and that's what SSIS uses by default for float.

It's also the case that MS SQL Server's double precision is more or less an IEEE 754 implementation. Well, the IEEE 754 representation of 7692.31 just happens to be 7692.31005859375 exactly. It's easy enough to find an IEEE 754 converter online to test that.

So, SSIS reads the value 7692.31 and stores it as a double-precision floating point number. Due to how IEEE 754 representation works, the number that SSIS actually stores is 7692.31005859375. Then it inserts that value when it pushes it to the database.

I'm not certain if that's where the problem happens, but somewhere in there with all the data conversion, some function is storing that number in it's IEEE 754 representation, and it's getting passed to SQL Server that way.