SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS inconsistent importing decimals from Excel spreadsheet


SSIS inconsistent importing decimals from Excel spreadsheet

Author
Message
peterzeke
peterzeke
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 1766
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



Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18782 Visits: 20458
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
peterzeke
peterzeke
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 1766
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.



Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18782 Visits: 20458
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
peterzeke
peterzeke
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 1766
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



Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1516
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
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 902
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search