Hello, all. I'm xfering data from a SQL 2000 table to an Oracle 10G
database. The table in question contains 41 million records, and is a faily
wide record. I've xfer'd this table from another SQL Server months ago, and
it ran within an hour...very fast indeed. However, my DTS xfer to Oracle is
running s-----l------o------w. It's noticably slower than my SQL-to-SQL
xfer. In fact, I started it yesterday before leaving work @ 5pm, and this
morning it's only xfer'd 1.2 million records.

Does anyone have advice for optimizing DTS to xfer to Oracle? Tips,
suggestions, etc are appreciated. BTW, I dropped the table's indexes to
speed things up, and the Oracle xfer is still slow.

Thanks.
Roz

I'm having the same exact issue.

"Roz" <Roz (AT) discussions (DOT) microsoft.com> wrote

I'm using a million rows as my test. It took less than 2 minutes to export
to flat file, ftp to the oracle box and use sql loader to push the records
into Oracle. The same test using DTS only takes 2 hours.

I have used Microsoft OLE, Microsoft ODBC, and Oracle ODBC.
I have changed all of the parameters about twenty times. No change.

Anyone have any clue on this one?



"Roz" <Roz (AT) discussions (DOT) microsoft.com> wrote

Unfortunately, I ended up using SQL Loader as well. Had to deal with the
cumbersome control file business, but it worked much faster than DTS. Next
week, I'm going to try using SQL 2005 Integration Services. I'll post back
if the results are faster than SQL 2000.

Roz

"TM" wrote:

Hi,
I am new to this group. We are trying to migrate Oracle 8 to 10g.
I am trying to validate the DTS packages, whether they work when we migrate to 10g.
Can anyone tell me, the neccessary checks i need to perform.


Thanks


From http://www.developmentnow.com/g/103_...o-Oracle--.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Hello Rajraj.atluri (AT) gmail (DOT) com,

I would probably check the Oracle release documentation. The changes will
be documented on the Oracle side in there. You can then determine if this
is likely to have an effect on you. You could then install new drivers (if
needed) to do the exports using DTS. I would then test your exports again.
This for me is as much about what Oracle has changed as much as it is about
what DTS might do.

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com