I have a database table which has a Decimal column called 'Rate'. The column definition is - Decimal (18,10).
Yes, the obvious question is why such a big precision. Well thats the requirement of the client - they store decimal values which are upto the precision of 10.
Data is loaded into this table from an Excel using a SSIS package. Unfortunately I am getting inconsistent results every time I run the SSIS package to load the data from the excel. For eg:
1) When the excel has values in the Rate column like 0.0092323882, 0.1121203232 ie, decimals starting with 0.something (no value on left of decimal) - these values are not loaded into the database.
2) Whereas whenever there are mixed values like 12.2323231, 0.4322199912, 0.2232166994, 4.223219932 - the values are successfully loaded into the database.
The data is loaded from the excel on a daily basis and most of the times the Rate column has values like 0.something.
I have setup the same package and database in SQL Server 2008 and every time the data is loaded successfully no matter whatever data is there in the excel.
Can anyone please advice -
- whether this is a limitation with SQL Server 2005?
- is there any fix to this issue?
- is there any other way I can over come this problem say, by using some other data type like float etc
Any help on this will be highly appreciated.