none
How to extract currency data into Excel without losing precision RRS feed

  • Question

  • Hello,

    I am trying to grab some data (including currency data) from Oracle to Excel in SSIS. So

    1) I create the table in Excel via 'Execute SQL Task' and Excel connection manager. - I think 'MONEY' should be the type for currency data. But let me know if this is wrong.

    CREATE TABLE `BBA_ByTask` (
    `FiscalYear` NVARCHAR(10),
    `Month` NVARCHAR(10),
    `ProjectNumber` NVARCHAR(10),
    `ProjectName` NVARCHAR(100),
    `Budget` MONEY,
    `CurrMonthExpenses` MONEY
    );

    2) Create a 'Data Flow Task' to pull data from Oracle, convert data types and send to an Excel worksheet. The task has three components, each doing the job I just mentioned.

    a. OLE DB Source - pull the data from Oracle. When I do preview, I can see currecny data with 2-digit precision.

    For example, budget = 78343.34


    b. Data Conversion - convert 'Budget' to 'currency[DT_CY]'

    c. Excel Destination

    However, when I open the Excel file, the precision is lost, kind of - see the '00' to the right of decimal point. budget = '$78343.00'

    Where I am doing wrong? How do you handle currency data in this setting? Thanks bunch for any help!
    Friday, May 27, 2011 7:37 PM

All replies

  • See if my blog post is helpful:

    http://geekswithblogs.net/Compudicted/archive/2011/03/28/how-to-import-negative-decimal-or-floating-values-to-flat.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 7:42 PM
    Moderator
  • This is a common problem with exporting to Excel and from what I've read in the past, the only way around this is to create a template spreadsheet with all your formatting, and then export data to the formatted spreadhseet.
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com
    Saturday, May 28, 2011 4:07 AM