I want to move 100G SQL Server 6.5 data to Oracle 9i r2. Data Transformation Services (DTS) looks like it is as fast as we thought. Moving 1M rows data for a 13 column table will take hours. What is the best tool to do this? I want to move 100 GB of SQL Server 6.5 data to Oracle 9i R2. Data Transformation Services (DTS) looks like it is as fast as we thought: Moving 1 MB of row data for a 13-column table will take hours. What is the best tool to do this?

    Requires Free Membership to View

    Login

First, I'm going to assume that you are able to use Data Transformation Service (DTS) under 7.0 or SQL Server 2000 even though your source data resides on SQL Server 6.5. (of course DTS doesn't exist in 6.5 instead we relied on BCP). Also, I'm assuming that you have the required Oracle ODBC driver so you can create a Data Source Name (DSN) which provides the Oracle connectivity details. Once you have this you can specify Oracle as the destination in your DTS package. Here are my recommendations for your DTS package.

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.

This was first published in May 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top