Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Bulk Copy Data from Oracle to SQL Server


By:   |   Last Updated: 2017-10-26   |   Comments (5)   |   Related Tips: More > PowerShell

Problem

Copying data from an Oracle table to a SQL Server table is not as simple as you would expect especially when you need to consider performance and flexibility to handle multiple sources.  Is there a convenient and quick way to accomplish moving data from Oracle to SQL Server?

Solution

There are multiple ways to bulk copy data from Oracle to SQL Server. In general, there are four options that I think SQL Server Professionals would be aware of:

  1. Use a tool such as Microsoft SQL Server Migration Assistant for Oracle.
  2. Use SQL Server Integration Services (SSIS)
  3. Create a Linked Server on SQL Server pointing to the Oracle database
  4. Export Oracle data to a CSV file and then import the data to SQL Server via bulk copy

Each option has its own pros and cons, but to me all these methods have one shortcoming in common, i.e. time-consuming to setup and develop.

In this tip, we will try to address this shortcoming by using PowerShell and the Oracle Data Provider for .Net, i.e. ODP.net, to quickly develop a solution to copy data from Oracle to SQL Server.

Before we start, we need to install ODP.net, which can be downloaded from here.

Install Package - Description: odp.net instalation file

ODAC Installation

I downloaded the zip file and then extracted the contents to a folder and here is what I had after extraction:

extraction_result - Description: the unzipped file list

After extraction, start a Command Prompt as administrator as shown below.

cmd_wind - Description: run command window as admin

Run the following command in the extracted folder:

install.bat odp.net4 c:\oracle odac true

Here is the screen shot:

install odp - Description: install oracle odp driver

After this, I will have all odp.net drivers installed under in my c:\oracle\ folder.  Actually all I need is just one driver, which is c:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll.

OracleMDA  - Description: OracleManagedDataAccess.dll location

Before we can use this dll in PowerShell, we need to unblock it. We can right-click it and then tick the “Unblock” checkbox as shown below:

Unblock_dll - Description: Unblock a DLL file after unzipping the file to a new location

Actually, I can run the following PowerShell script to unblock automatically:

Dir c:\oracle\odp.net\managed\common\*.dll | unblock-file

Data Preparation

Now we will prepare some test data. I actually installed an Oracle 11g R2 Express edition and Oracle SQL Developer tool on my laptop. In the SQL Developer tool, I can connect to my local Oracle database as shown below:

oracle_connection - Description: Connection information

For demo purposes, I composed the following PL/SQL to create and populate a table [tblTest] in Oracle.

create table tblTest (id INTEGER, dt DATE , name varchar2(100), sales NUMBER(8,2));
declare id integer := 0;
BEGIN
     while id < 1000 loop
          insert into tblTest (id, dt, name, sales)
            values (id, TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '1900-01-01','J')
                                    ,TO_CHAR(DATE '2099-12-31','J')
                                    )
                    ), 'J')
               , dbms_random.string('A', dbms_random.value(5, 99))
               , trunc(dbms_random.value(0.00, 1000.00),2));
        id := id + 1;
end loop; 
commit work;
END;

Let’s run a SELECT statement to check out the data:

select * from tblTest;

We will see the table is indeed populated:

source data in Oracle

On the SQL Server side (I have a SQL Server 2016 instance on my laptop), I will also create a corresponding table, note, in the Oracle table we have a [sales] column with a data type of NUMBER(8,2) and in SQL Server, I just changed it to a FLOAT data type for that column. I initially tried to use a DECIMAL(8,2) data type, but found this may cause some rounding issues for some records. For example, in Oracle, it shows 841.14, but after copying the data to SQL Server, the value becomes 841.13.

For detailed mapping between Oracle and SQL Server data types, please check the reference links in the Next Steps section.

use MSSQLTips  -- assume this MSSQLTips database exists
create table dbo.tblTest (id int, MyDate date, name varchar(100), sales float;

Data Transfer via PowerShell

By now, we have prepared both tables in Oracle and SQL Server, since the two tables are exactly the same in column sequence and compatible data types, we will first do a quick data transfer from Oracle to SQL Server, this is a table-wise transfer, i.e. each source column will be copied to its corresponding target column.

The script is actually very simple and short (10 lines without comments):

#copy table from Oracle table to SQL Server table
add-type -path 'C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll';
#define oracle connection string
$conn_str = "User Id=system;[email protected];Data Source=XE";
# query for oracle table
$qry = "select * from tblTest";
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $conn_str);
$dtbl = new-object System.Data.DataTable('tblTest');
#this Fill method will populate the $dtbl with the query $qry result
$adapter.Fill($dtbl);
#define sql server target instance
$sqlconn = "server=localhost\sql2016;database=mssqltips;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlbc.DestinationTableName="dbo.tblTest";
$sqlbc.WriteToServer($dtbl); 

After executing, let’s run the following SELECT query in SSMS:

use mssqltips
select * from dbo.tblTest;

I will get exactly the same result as we get from the Oracle table (of course, the date value is a little bit different):

result 1 - Description: copy data when table structures are exactly the same

Since the SQLBulkCopy class is very flexible, we can handle even more complex business requirements, such as when the source and the target tables may not be exactly the same. For example, my target table has more columns and/or the column sequence is not the same as the source Oracle table.

Let’s create the following table:

use MSSQLTips;
create table dbo.tblTest2 (id int identity primary key
, Sales float, Name varchar(100), SaleDate date
, LogDate datetime default current_timestamp);

In this case, we need to make the following column copy:

Oracle SQLServer
dt SaleDate
sales Sales
name Name

We can use the following PowerShell script:

#copy table from Oracle table to SQL Server table
add-type -path 'C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll';
#define oracle connectin string
$conn_str = "User Id=system;[email protected];Data Source=XE";
# query for oracle table
$qry = "select dt, sales, name from tblTest";
# key (on the left side) is the source column while value (on the right side) is the target column
[hashtable] $mapping = @{'DT'='SaleDate'; 'SALES'='Sales'; 'NAME'='Name'};
 
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $conn_str);
$dtbl = new-object System.Data.DataTable('tblTest');
#this Fill method will populate the $dtbl with the query $qry result
$adapter.Fill($dtbl);
#define sql server target instance
$sqlconn = "server=localhost\sql2016;database=mssqltips;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlbc.DestinationTableName="dbo.tblTest2";
 
#need to tell $sqlbc the column mapping info
foreach ($k in $mapping.keys)
{
    $colMapping = new-object System.Data.SqlClient.SqlBulkCopyColumnMapping($k, $mapping[$k]);
    $sqlbc.ColumnMappings.Add($colMapping) | out-null
}
$sqlbc.WriteToServer($dtbl);

Now if I run a SELECT statement on this new table, I will get exactly what is expected:

result 2 - Description: copy data when table structures are not same

Summary

In this tip, we learned how to copy data from Oracle to SQL Server via ODP.Net and PowerShell (Version 3+). The main advantage of this method, due to its small code footprint, is speed and flexibility in development plus low maintenance as compared to other approaches, especially SSIS in my opinion.

In some scenarios, such as dumping Oracle data to staging tables in SQL Server, we can further automate this approach by dynamically creating staging tables (i.e. without creating SQL Server tables first) or if we want to dump Oracle data to a CSV file, we can also easily achieve it with this approach.

In short ODP.Net provided a good channel for PowerShell enthusiasts to communicate with Oracle databases and have an elegant data exchange with SQL Server systems.

Next Steps

You can create a cmdlet based on the example in this tip and design the cmdlet parameters with your business requirements. For example, if the source and target table are the same in columns, the parameters can be just a table name (if source and target table names are the same).  However, if you need to select some source columns and copy to some target columns, you may need to add a parameter for column mapping information.  If you dump the data to a CSV file, you may add a parameter for the target CSV location.

You may also review the following articles to further understand the pros and cons of other approaches:



Last Updated: 2017-10-26


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 22, 2019 - 4:00:35 PM - jeff_yao Back To Top

 Thanks @Ed for reading the tip and give me feedback.

 According MSDN doc https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout?view=netframework-4.7.2,  you can set the properties of sqlbulkcopy object. In your script, I think you can do the following:

$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);

$sqlbc.bulkcopytimeout = 120; # 120 seconds

$sqlbc.batchsize = 1000; #change to your own
$sqlbc.DestinationTableName="dbo.tblTest";
$sqlbc.WriteToServer($dtbl);


Tuesday, January 22, 2019 - 2:16:50 PM - Ed Back To Top

I greatly appreciate your powershell code below - and it works perfectly for me.   I am copying from oracle to mssql with tables of the same name and structure.  But some of the tables are large and I would like to include the parameters for BatchSize and BulkCopyTimeout - but I cannot seem to get them to work - the script runs for a fairly long time and returns the correct number of rows but then issues a TIMEOUT message and does not write anything to the SQL table. 

 How to I get the script to write in batches and commit?    

Thank you.

#copy table from Oracle table to SQL Server table
add-type -path 'C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll';
#define oracle connection string
$conn_str = "User Id=system;[email protected];Data Source=XE";
# query for oracle table
$qry = "select * from tblTest";
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $conn_str);
$dtbl = new-object System.Data.DataTable('tblTest');
#this Fill method will populate the $dtbl with the query $qry result
$adapter.Fill($dtbl);
#define sql server target instance
$sqlconn = "server=localhost\sql2016;database=mssqltips;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlbc.DestinationTableName="dbo.tblTest";
$sqlbc.WriteToServer($dtbl);


Monday, March 12, 2018 - 4:49:15 PM - jeff_yao Back To Top

Hi @Paulo,

I have no immediate plan to create a multiple table copy version yet.

But I think you can make the script in this tip as a function with just probably a few input parameters, such as source table name and its connection string, and another is for target table name and target connection string.

When you want to do multiple table copy, you just need to loop through each source table and call the function by populating the correct target table name (assuming connection string values are not changing)

 

HTH,

jeff_yao


Monday, March 12, 2018 - 3:07:23 PM - paulo Back To Top

Hi

 

Very nice post.

Do you think of an variation of this script to allow multiple table to be transfered in a more automated way?

 

Thanks 

 


Thursday, October 26, 2017 - 11:34:42 AM - Jimbo99 Back To Top

 Yep, another way of doing what needs to be done. Kick it off and depending upon the size of the job/load, come back when it's done. Same issues data transfer rate and disk capacity.

 


Learn more about SQL Server tools