Data type conversion error Money-> text in excel destination

  • Hello All,

    My SQL data type is money, when I load the data into excel it takes the value as text but I want that data to be decimal or money to use for sum calculations in Pivots.

    suggestions please,.,.,

    Thanks,

    Ram

  • Have you used the Data Conversion in your Data Flow Task? Perhaps this link will be of some help for you.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • I did all the data conversions but the target excel file is taking all the values as text only, even if I change the data types in excel show advanced editor, it is not storing the values.

  • K, I'm currently trying to recreate the scenario but am having un-related issues at run time with my laptop. I'll get back to you in a bit or someone else will.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Alright I see what you mean.

    Questions:

    Is the Excel destination pre-existing or are you generating a new Excel file each time this particular data flow task is run?

    If the file is pre-existing then you should take the column in question that is to hold $$ data and format those cells appropriately.

    If the file is being generated at dft time then you may want to consider connecting to your personal Excel book. That particular book should be in a folder similar to the following path:

    C:\Documents and Settings\Your Name\Application Data\Microsoft\Excel\XLSTART

    Open that book, alter the column of cells where you know the $$ format should be, save it and close it. From there, every time an Excel workbook is opened it will open in that format. You can customize this Workbook however you see fit.

    I don't know if this is the best solution but it's all I have off the top of my head.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Thank you for your reply Mordred.

  • I just did as I suggested with my own starting Excel file and it didn't seem to have the proper effects. I'm going to keep trying though.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Alright so I formatted the columns that I am affecting with the data flow task and now it loads properly. Here's what I have done:

    1. Created a simple table called tblMoneys

    1.1 Created two fields in the table called ID & Money

    2. Created a personally formatted Excel file with headers ID & Money

    2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]

    2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]

    2.3 Saved and closed the workbook

    SSIS:

    1. Source - OLB DB

    2. Added a Data Conversion (although I don't think it was necessary but still used it non-the-less.

    3. Destination: Excel Connection Manager

    4. Ran the package

    No issues with the loading to Excel, all formats were good.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • 2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]

    2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]

    Hello Mordered, thank you for your reply.

    How did you set that currenct[DT-CY] data type??

  • For the layout I created in the Excel file, the currencies were placed in column B. I highlighted column B, right clicked, and then selected "Format Cells". I then chose Currency. When I made the destination connection to the Excel file, I checked the mappings and the format was as I stated.

    Which version of Excel are you using? The test I performed was with Excel 2010.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • I am using Excel 2007.

    I did the same thing as you did but the excel column shows the data type as unicode string [DT_WSTR].

    My excel file is not saving the data formats.

  • I don't know why your Excel file won't save the formats! I'm going to ask some Excel buddies/gurus that I know to see if they have any idea why your personal.xlsx file won't save the formats.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • I was advised that you shouldn't use your personal.xlsb file to do this. There are a couple of options that you could go with but it depends on the workbook you are using. For instance, what happens or will happen to the old workbook once a new data dump is done into the new workbook? Are you keeping each workbook used? If you are not and the data can be removed upon the next update then you can create a procedure (Macro) to delete the worksheet that the data will be stored on. You could create a template workbook to point to as part of your destination and then save it as a different name each time you update. Finally, you could multiply each currency cell in the excel workbook by 1 and that should do the trick.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • I am using template, first my package copies this template into another folder and then write the data into that.

  • OK, have you tried pre-formatting the template? Which column will hold the currency values? Do you know how to write VBA?

    Regards:
    Mordred
    Keep on Coding in the Free World

Viewing 15 posts - 1 through 15 (of 17 total)

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