Issue with a Decimal column in a table

Question

Issue with a Decimal column in a table

sumon.mukherjee

SSC-Addicted

Points: 487
More actions
April 4, 2010 at 9:08 am

#139071

Hello All,
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.
Thanks,
Sumon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 1

Hi,

Could you please try using a Data Conversion task in between Excel source and table destination. And in this task give the required format for the column.

Regards,

MC

Thanks & Regards,
MC

sumon.mukherjee SSC-Addicted Points: 487 More actions · Answer 2

Many thanks for your reply.

As mentioned earlier, I am loading the data from an excel using a SSIS package. I use a SQL task to load the data. Below is the SQL Script which does the actual job -

INSERT INTO [xxxTable]

([ID],[Loop],[Name],[Inserted],[Deleted],[Contract],[Start_Date],[End_Date],[Num_of_Ads],[Rate],[Advertiser],[Agency],[Product])

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',

'Data Source=E:\Test\currentplaylist.xls; extended Properties=Excel 8.0')...cumulative$

WHERE [FILE #] is not null

The field 'Rate' is the Decimal field. Can you please help me out how can I use the data conversion in this case?

Thanks Again,

Sumon

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Use data conversion task instead of sql exec task.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.