Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS inconsistent importing decimals from Excel spreadsheet Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 8:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
We're running into an odd situation where SSIS won't always import a numeric value from Excel exactly as the value in Excel.

For example:
the decimal value 10.4 will import as 10.3999999999999, while 10.5 will import correctly as 10.5. Whole integers seem to work ok, too.

Any ideas what's going on? Myself and a colleague are a bit stumped.

--Pete



Post #1478051
Posted Friday, July 26, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 5,023, Visits: 11,752
Could it be that formatting has been applied in Excel to display the value to 2 decimal places?

Try exporting from Excel to CSV and then using Notepad to see what's really there. Don't believe Excel



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1478054
Posted Friday, July 26, 2013 9:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
Thanks for such a quick reply, Phil.

As you suggested, I exported a spreadsheet as a text file. The values in the text file match the values in the spreadsheet (i.e., 10.4 in Excel exported as 10.4 in the text file), but the decimal data loaded via SSIS ends up wacky in the sql destination table.

6.4 loaded as 6.4000000000000004
5.8 loaded as 5.7999999999999998
0.58 loaded as 0.57999999999999996
0.8 loaded as 0.80000000000000004

but
10.1 loaded as 10.1
4 loaded as 4.

The SQL destination column is nvarchar since any column of data in excel might have a text value rather than a numeric/decimal value. We have data entry validation rules applied to the spreadsheet, specifically regarding min/max decimal values, but, once in a while a text value needs to be entered and therefore the data validation is removed for that specific cell.




Post #1478062
Posted Friday, July 26, 2013 9:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 5,023, Visits: 11,752
peterzeke (7/26/2013)
Thanks for such a quick reply, Phil.

As you suggested, I exported a spreadsheet as a text file. The values in the text file match the values in the spreadsheet (i.e., 10.4 in Excel exported as 10.4 in the text file), but the decimal data loaded via SSIS ends up wacky in the sql destination table.

6.4 loaded as 6.4000000000000004
5.8 loaded as 5.7999999999999998
0.58 loaded as 0.57999999999999996
0.8 loaded as 0.80000000000000004

but
10.1 loaded as 10.1
4 loaded as 4.

The SQL destination column is nvarchar since any column of data in excel might have a text value rather than a numeric/decimal value. We have data entry validation rules applied to the spreadsheet, specifically regarding min/max decimal values, but, once in a while a text value needs to be entered and therefore the data validation is removed for that specific cell.



OK - can you also confirm that the datatype of the column in the SSIS pipeline is varchar(n)?

If so, can you put a data viewer somewhere in the data flow to check whether the value has already changed by the time it comes into SSIS?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1478068
Posted Friday, July 26, 2013 10:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
We applied a data viewer immediately after the excel data connector, and could see that the values are distorted coming out of the connector before being handed off to a derived column object prior to the destination table.

So, it seems the excel connector is causing the problem. We investigated the field definitions (meta-data) in the excel connector (advanced editor), and the fields are defined as string.

What we've also discovered, however, is that if a column in the spreadsheet only contains numeric values, then decimals will end up distorted in SSIS (e.g., 10.4 becomes 10.400000004). But, if we have a cell with text info along with numeric values in the same column, the numeric values will not be distorted. Apparently, one string value in a column forces SSIS to accept all values in a column as text, but if only numeric values exist then SSIS will change the decimal values (provided that the decimal value doesn't end with a 5 -- i.e., 10.5 remains as 10.5, 2.05 remains as 2.05 -- but any other ending decimal will distort in the package). Weird.

--Pete



Post #1478096
Posted Friday, July 26, 2013 4:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:27 PM
Points: 185, Visits: 923
It's a standard behaviour of an Excel driver + SSIS. There is not much you can do about it. You have to round all the numeric values in SSIS dataflow.


Alex Suprun
Post #1478240
Posted Thursday, August 8, 2013 4:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Excellent!

Yet another good reason not to use Excel as a datasource with SSIS.

I can't believe how badly these two MS flagship products play together. Each is excellent in its own right but whenever I link them I have issues
Post #1482606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse