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

Posted on 2014-08-14
Medium Priority
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 43

Expert Comment

by:Eugene Z
ID: 40262078
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
ID: 40262219
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 43

Accepted Solution

Eugene Z earned 1000 total points
ID: 40262960
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

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 1000 total points
ID: 40276122
An alternative way of doing is by using sql developer. Below link explains the whole method.


Maybe you can try this.
LVL 23

Expert Comment

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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to recover a database from a user managed backup
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

578 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