Here is a good question for you senior DBAs.

You have to DTS 20 million rows nightly from SQL 2000 to Oracle 9.2.0.

How would you do it?

Here is what I did and some timings.

20 million rows.
SQL Server DTS using ODBC or OLE DB [took hours]
..csv to Oracle using ODBC [took hours]
SQL Server DTS write .csv file, FTP to oracle box, sqlloader, [10 minutes]
Oracle to SQL [1 hour]
Oracle to .csv [10 minutes]


Does anyone know if using OpenQuery plus DTS will speed this process up?

I see ETL tools load from SQL to Oracle quickly using the native calls. Can
anyone give examples of how I might accomplish the same using DTS?

I am shocked at how little information is out there concerning loading
larger tables inside DTS to Oracle.

Does anyone know if this performance issue was resolved in SSIS?

Thanks in advance for any information you can give. Threads in forums
related to this seem to get unanswered and vanish. At this point, I want to
get some answers and push the information back to the SQL community by
mirroring the solution in different forums.

Hello TM,

I would be inclined to throw the rows to flat file on disk, FTP the file
across, and use SQL*Loader to do the import.

The first two can be called through DTS

The SQL*Loader then have it look for a file at intervals and import.


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

That's been the general statement I have been getting from most.

Day Two:

Talked with at least 5 different DBA types, and everyone is still saying FTP
and SQLLOAD. I hope this isn't the same in SSIS.

Hello TM,

There are a few new Oracle related products coming online for SSIS. Maybe
these will be quicker for you. I also believe the more recent versions of
Oracle are able to see a Flat File as an external table so maybe this is
an option for you as well.

http://www.persistentsys.com/product...oracleconn.htm


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com