How to migrate a  table data from SQL Server 2008 to Oracle 11g database?

Posted on 2014-08-14
Last Modified: 2015-06-23
Hi Experts,

I am trying to migrate SQL Server 2008 table records to an Oracle 11g database using the SQL Server Import and Export Wizard. The  Oracle Database is running Linux system

I did the following  steps for the migration.

1. Select the SQL Database -> Right Mouse click
2. tasks - > export data
3. Choose a data source (keep the default options)
4. Click "Next" button
5. Choose a Destination: (Oracle Provider for OLE DB)
6. Select the table and click Finish Button

There are millions of records in the table, but after copying a couple of million records, SQL Server Import and Export Wizard is crashing.

Please let me know the bestion solution for requirement.

Thank You
Question by:Ullan P
    LVL 42

    Expert Comment

    what is your sql server 2008 service pack
    you may need to install latest

    what is the error you got?

    check this solution:

    Edit the file "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe.config" (run Notepad, etc. as Administrator), and comment out/delete <supportedRuntime version="v4.0"/>. Leave the line about v2.0.50727 alone.


    Author Comment

    by:Ullan P
    The wizard just crashed with out showing any error message..

    Earlier  tried your first solution, but in my environment,  the entry  <supportedRuntime version="v4.0"/>. does not exist in the file.

    There is one corretion : The SQL Server version is :  2005

    Microsoft SQL Server 2005 - 9.00.5057.00 (X64)   Mar 25 2011 13:33:31   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
    LVL 42

    Accepted Solution

    you can try to:
    -use a "real" SSIS package. Create a data flow in the SSIS package, and add an OLE DB Source to the dataflow to select from the original table. Then add an OLE DB Destination and configure the Maximum Insert Commit Size to be a reasonable batch size for your system.
    //see SQL Server Integration Services with Oracle Database 10g

    --or try to use sql server linked server to push data into Oracle schema
    or pull it from oracle
    se more

     ---use bcp utilty instead of the EI GUI

    Importing and Exporting Bulk Data by Using the bcp Utility
    LVL 5

    Assisted Solution

    by:Sanjeev Labh
    An alternative way of doing is by using sql developer. Below link explains the whole method.

    Maybe you can try this.
    LVL 22

    Expert Comment

    by:Steve Wales
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now