July 26, 2013 at 8:47 am
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
July 26, 2013 at 8:49 am
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:-)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 26, 2013 at 9:13 am
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.
July 26, 2013 at 9:32 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 26, 2013 at 10:16 am
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
July 26, 2013 at 4:36 pm
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.
August 8, 2013 at 4:23 pm
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 :w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply