Issue with a Decimal column in a table

  • sumon.mukherjee

    Old Hand

    Points: 347

    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

    SSCrazy Eights

    Points: 8216

    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

    Old Hand

    Points: 347

    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: 66773

    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.

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

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