Export MONEY datatype to Oracle

  • I'm exporting a table from SQL Server 2008R2 to Oracle 11g. One of the datatypes in the SQL Server database is MONEY. The receiving column in the Oracle table is defined as NUMBER(12,4), based on this: http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm

    When I export the table through the 'Export Wizard' and then compare, I get equal values. There are 2.5 million records in the table and it takes over 8 hours to load. I made sure to save as an SSIS package for later use. When examining the package, the 'Data Conversion' task does NOT use an alias to convert the MONEY column.

    To minimize the load time, I opted to use the Attunity drivers for Microsoft and an SSIS "Oracle Destination" task that comes with those drivers. The package consists of the following tasks:

    OLEDB Source

    Data Conversion

    Oracle Destination (from Attunity)

    In order to get past pre-execute errors, the 'Data Conversion' task uses an alias to change the datatype from [DT_CY] (currency) to [DT_NUMERIC]. The load takes about 10 minutes but the amounts between the two databases are not equal.

    In SQL Server, I created copies of the table but re-defined the MONEY datatype to NUMERIC(12,4) and DECIMAL(12,4). In the 'Data Conversion' task I defined the columns as [DT_NUMERIC]. Neither yielded equal values.

    I realize this is more of an Attunity question, but I'm hoping someone may have some experience with this. Is there a different way to do this? I need to reduce the load time and have equal values.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Hi

    I am facing similar kind of problem . Please let me know if you came across any solution for this.

    Thanks

  • SQLDCH (5/31/2012)


    I'm exporting a table from SQL Server 2008R2 to Oracle 11g. One of the datatypes in the SQL Server database is MONEY. The receiving column in the Oracle table is defined as NUMBER(12,4), based on this: http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm

    When I export the table through the 'Export Wizard' and then compare, I get equal values. There are 2.5 million records in the table and it takes over 8 hours to load. I made sure to save as an SSIS package for later use. When examining the package, the 'Data Conversion' task does NOT use an alias to convert the MONEY column.

    To minimize the load time, I opted to use the Attunity drivers for Microsoft and an SSIS "Oracle Destination" task that comes with those drivers. The package consists of the following tasks:

    OLEDB Source

    Data Conversion

    Oracle Destination (from Attunity)

    In order to get past pre-execute errors, the 'Data Conversion' task uses an alias to change the datatype from [DT_CY] (currency) to [DT_NUMERIC]. The load takes about 10 minutes but the amounts between the two databases are not equal.

    In SQL Server, I created copies of the table but re-defined the MONEY datatype to NUMERIC(12,4) and DECIMAL(12,4). In the 'Data Conversion' task I defined the columns as [DT_NUMERIC]. Neither yielded equal values.

    I realize this is more of an Attunity question, but I'm hoping someone may have some experience with this. Is there a different way to do this? I need to reduce the load time and have equal values.

    Ummm.. that document you referred to specifies NUMBER(19,4) as the proper destination for the MONEY data type, and for SMALL MONEY, NUMBER(10,4). How would 12,4 work when 19,4 is needed?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • yes really I accept this point.Export money datatype is reduce in my loading time.I analysed.I hope this information.http://www.trainingintambaram.in/web-designing-training-in-chennai.html

  • yes really I accept this point.Export money datatype is reduce in my loading time.I analysed.I hope this information.http://www.trainingintambaram.in/web-designing-training-in-chennai.html

  • aaidanmary (7/27/2015)


    yes really I accept this point.Export money datatype is reduce in my loading time.I analysed.I hope this information.http://www.trainingintambaram.in/web-designing-training-in-chennai.html

    I see no relevance to the problem associated with the link you provided. I wonder what you would get if you exported the values to a text file or text data type using those same drivers. It makes me wonder if it's rounding the values to save load time, in which case you can forget about making viable use thereof...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • am having some load problem. now only analysis to load files reason . thanks admin . keep on update same valuable information

  • sgmunson (7/27/2015)


    aaidanmary (7/27/2015)


    yes really I accept this point.Export money datatype is reduce in my loading time.I analysed.I hope this information. (EDIT: removed URL)

    I see no relevance to the problem associated with the link you provided. I wonder what you would get if you exported the values to a text file or text data type using those same drivers. It makes me wonder if it's rounding the values to save load time, in which case you can forget about making viable use thereof...

    It looks like spam.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 8 posts - 1 through 7 (of 7 total)

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