May 31, 2012 at 11:33 am
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
----------------------------------------------------------------------------
February 10, 2015 at 9:14 am
Hi
I am facing similar kind of problem . Please let me know if you came across any solution for this.
Thanks
July 13, 2015 at 9:38 am
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.htmWhen 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)
July 27, 2015 at 5:30 am
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
July 27, 2015 at 5:32 am
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
July 27, 2015 at 10:13 am
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)
September 16, 2015 at 5:51 am
September 16, 2015 at 6:30 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply