261 Views
5 minute read
Categories
Data Migration

How To – Bulk Copy Data from ORACLE to SQL Server

Case Study:

As part of migration activities some users may require transferring very large sets of data from a Remote Oracle DB server to another Microsoft SQL Server Database.

To make it a bit more challenging, my task involved a bit more complexity to move millions of records from large number of tables without any discrepancy and within an acceptable timeline.

Technical Options:

There are numerous approaches to bulk copy records from Oracle to SQL Server. Following are a few options which may be considered.

  1. Microsoft SQL Server Migration Assistant for Oracle
  2. Import Using Oracle Client and SQL Server Management Studio
  3. Linked Server on SQL Server pointing to the Oracle database
  4. Export to CSV file and import to SQL Server via bulk copy

There may be driving factors to choose one option over the other.

Of the above listed options, we preferred executing with Option #2.

In following few sections I will demonstrate the basic requirements, system setup and configurations required to transfer the data.

Pre-requisites:

Important Note: During our evaluation, we faced some compatibility issues between Oracle Database Instant Client 64 bit and SQL Server Management Studio during connectivity, so we switched back to 32 bits of Oracle Instant Client. So, it’s suggested to use the 32 bit version.

Instructions for installing Oracle Instant Client on Windows:

  1. Create a directory for the Oracle client components e.g., “c:\oml4rclient_install_dir”
  2. Go to the Oracle Database Instant Client download page.
  3. In the “Instant Client for Microsoft Windows” section choose Instant Client for Microsoft Windows (32-bit).
  4. From the next page download the “Basic Package” and Save the file in directory created in Step 1
  5. Unzip the file. The files are extracted into a subdirectory called instantclient_version, where version is your version of Oracle client. e.g., c:\oml4rclient_install_dir\instantclient_19_9
  6. Return to the Instant Client Downloads for Microsoft Windows (x64) page.
  7. Accept the license agreement and select Instant Client Package – SDK for your version of Oracle Database.
  8. Save the file in the installation directory that you created in Step 1.
  9. Unzip the file. The files are extracted into the instantclient_version subdirectory.
  10. Add the full path of the Instant Client to the environment variables OCI_LIB64 and PATH. The following steps set the variables to the path used in this example, c:\ oml4rclient_install_dir\instantclient_19_9:
  11. a. In Windows Control Panel, choose System, then click Advanced system settings.
  12. b. On the Advanced tab, click Environment Variables.
  13. c. Under System variables, create OCI_LIB64 if it does not already exist. Set the value
  14. of OCI_LIB64 to c:\ oml4rclient_install_dir \instantclient_19_9.
  15. d. Under System variables, edit PATH to include c:\ oml4rclient_install_dir\instantclient_19_9.

Steps to Import data:

After all the system setup is done, follow the below steps to import the data.

  • Open the SQL Server management studio and connect to the SQL Server.
  • Create a new database with a suitable name.
  • Right Click on the newly created database and select Task-> Import Data
How To – Bulk Copy Data from ORACLE to SQL Server
  • Click Next
How To – Bulk Copy Data from ORACLE to SQL Server
  • Select “.Net Framework Data Provider for Oracle” from list of options available from Data source dropdown.
How To – Bulk Copy Data from ORACLE to SQL Server
  • Under Security section provide oracle username and password.
  • Under Source section enter the Data Source in following format:
  • [Oracle Server URL:Port(if not running on default port) / NameSpace]
  • Click on next. On successful connection with the oracle server, it will proceed to the next screen
  • Select Destination as SQL Server Native Client, Server Name, SQL Server Credential and Database. Click Next.
How To – Bulk Copy Data from ORACLE to SQL Server
  • From the next screen select “Copy data from one or more tables or views” and click on Next.
How To – Bulk Copy Data from ORACLE to SQL Server
  • From the next screen select all the tables or only the tables which are required.
  • Click on Edit Mappings Button and from the popup window select destination schema name. In our case selected the SQL Server default ‘dbo’ schema.
How To – Bulk Copy Data from ORACLE to SQL Server
  • Check Run Immediately checkbox and click on the Next button.
How To – Bulk Copy Data from ORACLE to SQL Server
  • On the final screen Click on the Finish button to start coping of data.
How To – Bulk Copy Data from ORACLE to SQL Server
  • Once done Explore the SQL Server Database and verify the data with Oracle DB.

Summary:

Our choice of copy option was driven by the following factors:

  • Very less setup of activities
  • Coping of data from multiple tables is fully automated, hence there is little chance of data discrepancy
Subhankar Roy

About Subhankar Roy

Technically sophisticated and programming savvy web professional with a pioneering career reflecting strong devotion for Internet-based firm in a web developer capacity. Maintain focus on achieving bottom-line results while formulating and implementing advanced technology to meet a diversity of needs. Superior record of delivering simultaneous, large-scale, and mission-critical projects on time. Team-based management style and excellent interpersonal/communication skills.

LinkedinTwitterFacebook

Leave a Reply

Your email address will not be published. Required fields are marked *




Enter Captcha Here :