You may consider partitioning the data. Instead of having a single data pump in your DTS package, have 5, 6 or 7 data pumps that can run in parallel. Look at the source data column and see if there is a way to horizontally partition the data based on a source column. If your source data includes a date time column, this may be an ideal candidate. See if you can partition the data by the year quarter, months, etc. I've also seen data partitioned based on geographical regions.
Design your package so it executes these multiple transfers (data pumps) in parallel. When you execute the package while in design mode the tasks that are currently executing will be identified by a green execute arrow and a row count will be displayed. Your goal should be to have all data pumps run in parallel.
Experiment with moving subsets of data, in which you transfer the data from SQL Server to an intermediary text file then to Oracle. You may see that rows can be transferred much faster using a file that sits between the SQL source and Oracle destination.
Dig Deeper on SQL Server Interoperability
Related Q&A from Joe Toscano
Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed. Continue Reading
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ... Continue Reading
Getting an error when importing data from an Excel spreadsheet to SSIS in SQL Server 2005? Try lining up data types in Excel and SSIS so they match. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.