I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL Server...

There are approximately 10 flat files to export then insert before setting off the updates. Each file contains around 3.5 million rows and 5 to 10 columns per file.

Everything now is done strictly through stored procedures and I'm looking into SSIS assuming the permissions are properly granted this week. I've also come across Rhino ETL.

Does anyone have advice on efficient though relatively painless ETL from Oracle to SQL Server?

Appreciate the help!


4 Answers 4


SSIS does a good work of exporting data from Oracle to SQL Server. I suggest you create staging tables in SQL Server and use SSIS to extract the data for the 10 Oracle tables. Then either use stored procedures in SQL Server and execute them using SSIS or use Merge syntax and try to do both insert as well as update in Control Flow. Updates tend to be slow in Data Flow task of SSIS using Oledb command for very large number of rows.

Go through this link Using SSIS to get data out of Oracle: A big surprise! which explains the performance of SSIS while loading fairly large amount of data in a short time.

To get started for ETL process from Oracle to SQL Server try to read this documentation.

  • I agree with praveen... my vote up! Commented Mar 4, 2013 at 16:23

I have several easy to apply advises, drawn from my own personal experiences when "pulling" data from a transactional Oracle database into a "staging" database (under Microsoft SQL Server 2012 / 2014):

  1. In the SSIS packages do strive to use Oracle's own OLE DB Provider.
  2. Likewise, use Microsoft's latest OLE DB Provider for the ADO Destination.
  3. REDUCE (yes, you read right) the Microsoft's Packet Size from 8000 down to 4000 (an arbitrary value that has done wonders on rather large data tables, especially when developing on a Windows 7 Desktop).
  4. Try to use DML SQL queries instead of just using the "pulldown" table/view list from your source connection. It speeds up transfers.
  5. (Optional) do revise said query and remove those columns that you don't have any use for in your destination database, especially those that "flag" what data you can pull from the source. This last tidbit is often overlooked more times than you can guess. Hope these advices help to improve your transfer times!

I used Data Manager from IBM and it was messy. So, I wrote a simple Java program to do the migration instead and it worked like a charm.


We are using Linked server to connect to Oracle through OLE DB. the using openquery command to pull data down to SQL server.

This will help

  • 1
    Could you please clarify your second sentence? Commented Feb 4, 2016 at 17:00
  • This is a really slow way to go. A linked server is fine for a small query, but not moving large amounts of data. SSIS is the way to go.
    – april4181
    Commented Jul 7, 2016 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.