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


Issue with a Decimal column in a table


Issue with a Decimal column in a table

Author
Message
sumon.mukherjee
sumon.mukherjee
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 57
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
only4mithunc
only4mithunc
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7554 Visits: 2812
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
sumon.mukherjee
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 57
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
PaulB-TheOneAndOnly
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

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