Export SQL Server Data to Oracle using SSIS

By:   |   Updated: 2010-05-18   |   Comments (23)   |   Related: 1 | 2 | 3 | More > Integration Services Oracle


Problem

I have some SQL Server data that I need to export and load into an Oracle database.  In this tip we cover how this can be done with SSIS.

Solution

SQL Server Integration Services (SSIS) would be a good choice for getting data out of a SQL Server database and loading it into an Oracle database.  SSIS was first released with SQL Server 2005; prior to that SQL Server included a tool called Data Transformation Services (DTS) which would also work.  Since DTS is a tool that was included with SQL Server 7 and SQL Server 2000, we'll use SSIS in this tip.

SSIS is a tool that is typically used in the extract, transform and load (ETL) process in a data warehouse.  It provides a graphical designer that allows you to easily pull data from one database, perform some transformations on the data, then load it into another database.  For a detailed introduction to SSIS, take a look at our SQL Server Integration Services Tutorial.

In this tip we will use the SQL Server Import and Export Data wizard which will walk us through the steps to export data from a SQL Server database and load it into an Oracle database.   In addition the wizard will automatically generate an SSIS package that can be run using DTEXEC.EXE or DTEXECUI.EXE.  DTEXEC is a command line tool for running an SSIS package; DTEXECUI provides a graphical user interface for running an SSIS package.  Both utilities are included with SQL Server.

Setup

I have a virtual machine with SQL Server 2008 Developer Edition, Oracle 10g, and the Oracle 9i client.

Sample Database

We need a sample SQL Server database.  I'm sure we're all  tired of seeing the pubs, northwind and adventureworks sample databases.  To break the monotony we'll use the Chinook sample database which you can retrieve from the CodePlex site.  The Chinook database contains tables for artists, albums, media tracks, etc.  It contains scripts to create and load multiple databases: SQL Server, SQL Server Compact, Oracle and MySQL.

Import and Export Data Wizard

You launch the Import and Export Data wizard from the SQL Server program group.  After an initial welcome screen, you will see the Choose a Data Source dialog as shown below:

launch the Import and Export Data wizard from the SQL Server program group

SQL Server Native Client 10.0 is the default data source; this is what you want for a SQL Server 2008 database.  Windows Authentication is also the default; this means you will execute the SSIS package as the currently logged on user.  Alternatively you can select SQL Server Authentication and supply a username and password.  Select Chinook from the Database dropdown.

After clicking Next to continue you will see the Choose Destination dialog as shown below:

SQL Server Native Client 10.0 is the default data source; this is what you want for a SQL Server 2008 database

The Destination will also default to SQL Server Native Client 10.0; select Oracle Provider for OLE DB from the dropdown.  Click the Properties button to enter the details about the Oracle destination as shown below:

The Destination will also default to SQL Server Native Client 10.0

The Data Source is defined in the tnsnames.ora file for the Oracle client.  I have the Oracle 9i client installed in the folder D:\OraClient9i so the tnsnames.ora file will be in the D:\OraClient9i\Network\Admin folder.  An example of the tnsnames.ora file is shown below:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = BARLEY-890AE132)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Click OK on the Data Link Properties dialog then click Next.  You will see the Specify Table Copy or Query dialog as shown below:

You will see the Specify Table Copy or Query dialog

Click Copy data from one or more tables or views; this will allow you to select tables and views to export from the SQL Server database.  The Write a query to specify the data to transfer will allow you to enter your own query to select the data that you want.

After clicking Next you will see the Select Source Tables and Views dialog as shown below:

select tables and views to export from the SQL Server database

I selected all of the tables in the Chinook database.  You can select a row then click the Edit Mappings button to display the Column Mappings dialog as shown below:

select a row then click the Edit Mappings button to display the Column Mappings dialog

The above dialog allows you to drop the destination table and edit the SQL to create the table.  You can also edit the Source to Destination column mappings.  Click on a Destination column and you can change it by selecting a different column in the dropdown.  Click OK to close the dialog then click Next; you will see the Save and Run Package dialog as shown below:

allows you to drop the destination table and edit the SQL to create the table

Click the Run Immediately checkbox to execute the SSIS package; click the Save SSIS Package checkbox to save a copy of it to either SQL Server or the file system.  As a general rule you don't want to store passwords unencrypted in an SSIS package.  The Package protection level dropdown has various encryption options.  For our purposes we're going to select "Do not save sensitive data" which will not save the password in the SSIS package.  Click Next and you will see the Save SSIS Package dialog as shown below:

click the Save SSIS Package checkbox to save a copy of it to either SQL Server or the file system

Name is the filename for the SSIS package.  You can click Browse to navigate to the folder where you want to save the SSIS package.  Click Next and you will see the Complete the Wizard dialog as shown below:

click Browse to navigate to the folder where you want to save the SSIS package

Click Finish to execute and save the SSIS package.  You will see the following dialog (the screen shot was taken after the SSIS package finished):

the screen shot was taken after the SSIS package finished

Review the SSIS Package

SQL Server includes the Business Intelligence Development Studio (BIDS) which is where you can use the graphical designer to create and maintain your SSIS packages.  Launch BIDS from the SQL Server program group; select File, Open, File from the menu and navigate to the folder where you saved the SSIS package and open it.  The following is an example of the SSIS package that is generated:

Launch BIDS from the SQL Server program group

The Preparation tasks each create a group of tables in the Oracle database.  The Data Flow tasks copy the data from a group of SQL Server tables to the Oracle tables.  You can add whatever kind on logic you need to the SSIS package that gets generated.  

Back on the Column Mappings dialog I clicked Drop and re-create destination table for the Album table.   However, I did not see where the SSIS package dropped the table before creating it.  If you need to drop the tables in the Oracle database, you can add a new Execute SQL task as the first task in the package and drop the tables.

Next Steps
  • The Import and Export Data wizard is a good starting point when you need to copy a group of tables from one database to another.
  • You may need to do more than just copy data from one database to another.  The Import and Export Data wizard generates an SSIS package that you can modify as necessary.
  • For an introduction to SSIS, take a look at our our SQL Server Integration Services Tutorial.
  • To dig in to more details on SSIS, take a look at our SQL Server Integration Services Tips.
  • You can download the sample package from this tip here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-05-18

Comments For This Article




Wednesday, December 6, 2017 - 7:55:55 AM - Ray Barley Back To Top (73669)

According to this post - http://www.oracle.com/technetwork/database/windows/index-089115.html - Oracle Provider for OLE DB 12.1.0.2 now supports the IRowsetFastLoad interface, which enables fast memory-based bulk-copy operations in the Oracle database.

You would need to use this later version of the Oracle Provider for OLE DB I htink.  This post is several years old and the
Oracle Provider for OLE DB 12.1.0.2 was not available at that time.

Monday, December 4, 2017 - 7:57:19 PM - Luis Agustin Azario Back To Top (73623)

Great post !!! Can you tell me if this DTSX generate eficient code for Oracle as a bulk insert or it creates an insert by insert solution ?

Thank you

 

Tuesday, April 4, 2017 - 2:57:55 PM - the-man Back To Top (54234)

hi

is there any best practice tutorials about Sql Server to Oracle ETL? (i googled and found nothing)

my problem is:

I have N sql severs (v 2012) as a source (some tables have about 1milion rows), and one oracle database as e datawareHouse (11g)

I cant use FastLoad or Merge command and I delete whole tables and them insert again every night! and for some tables use Max(Id) (i'm novie to this, but I cant find the best practices for sql server 2 Oracle yet)

I'm having trobule in memory usage (sometimes get fault and low memory erros) and speed.

but what is the best scenario?

Wednesday, November 25, 2015 - 1:06:15 PM - Edison Back To Top (39143)

I have this error with Properties Oracle Provider for OLE DB:

 

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle. (SQL Server Import and Export Wizard)

Provider cannot be found. It may not be properly installed. (ADODB.Properties)

 

I've installed Oracle client and odac64 component, and register this path component in path of windows system.

 

Friday, July 17, 2015 - 8:24:00 AM - Ray Barley Back To Top (38236)

My first thought is when you say you get table doesn't exist error are you connecting to oracle with the same account you used in the connection manager in the SSIS package?  

 

Also you should connect to oracle using the same account you used in the connection manager in the SSIS package and verify that you can create a table, insert a row, do a select, etc.

Friday, July 17, 2015 - 6:22:03 AM - umashankar bharamgonde Back To Top (38232)

i have imported data from sql server 2008 to oracle 11g using ssi package i can see tables in hr schema where i imported but when i am trying to see weather those table have all columns in it.facing table or view does not exist error

 

while using ssis package test connection was good /successful and import is also successfull

 

kindly help what is the issue why i am not able see all those tables in oracle HR schema i issued query as sysdba as well as Hr user

Tuesday, March 24, 2015 - 11:07:58 AM - Raymond Barley Back To Top (36657)

I do not have an Oracle instance available right now so I can describe what I would do based on my understanding of what you are trying to do (referring to the comment Monday, March 23, 2015 - 5:03:19 PM - Omow below)

Create a connection manager for your oracle instance; Type=ADO.NET, Provider= .Net Providers\OracleClient Data Provider

Add a Data Flow

Add an ADO .NET Source to the data flow; specify the oracle connection manager, select Data Access Mode = SQL Command, specify a query; you probably can do this using a common table expression rather than dealing with temp tables

Add an ADO .NET Destination to the data flow; specify the oracle connection manager, specify the Oracle destination table, and map the columns from the ADO .NET Source to the Oracle destination table columns

 

Monday, March 23, 2015 - 5:03:19 PM - Omow Back To Top (36645)

Hi this was very helpful. I have a similar issue. Would you help me out with it?

-- I have to create temp table in SSIS for oracle DB then do two different lookups. One to combine two fields by joining two different table and get a new unique value and the other to do lookup no match on the new unique value with the old one and load the data into oracle destination? The soure and destination both are in oracle databse. Any solution?

 

Thanks on advance

Saturday, April 12, 2014 - 10:17:48 PM - rajesh Back To Top (30059)

img  and text datatype data is not exporting to oracle showing error "data type not supported"

 

Please give me suggestion that how to export data from sql server 2008r2 to oracle 11 that contains img and text datatypes..

 

thanks in advance...

Wednesday, July 24, 2013 - 7:29:36 AM - Loki Lokaj Back To Top (25974)

Hello I have one problem with the wizard. I wanna import data to oracle DB from sql server. Everything goes fine but when I get to "Selecet Source Table and Views" it displays in center of window 16 objects loaded and wizzard goes not responding. It happens all the time i get there. So im pretty stucked there. Im using sql server 2012 and coracle client v. 11. I am not admin in target oracle DB but have privilegies for basic operations. Its not many information but if u have idea what may cause this problem i will be very thankfull. Thanks:)

Tuesday, January 29, 2013 - 3:32:51 PM - Ray Barley Back To Top (21778)

You will find some compatability issues between the two databases.  SSIS has a number of data flow transforms that you can use to massage the SQL Server data types as necessary; e.g. derived column.  You can also use user-defined functions to accomplish the same thing.  Finally when all else fails a script component may be necessary to allow you to write .NET code to do the transforms.

 

Tuesday, January 29, 2013 - 2:29:46 PM - kerany Back To Top (21776)

Hi Ray,

I have found some problem when i want to export sqlserver data to Oracle , i discover that i have to change some  data type like NVARCHAR(4000), timestamp, and i'm much concerned about the data integrity in new oracle database as nowdays i work on  database production

best regards

 

thanks

Tuesday, January 22, 2013 - 11:57:59 AM - Ray Barley Back To Top (21622)

It's possible that using the native utilities to export and import data will be faster.  SQL Server comes with a utility called BCP that you can use to export a table (or the results of a query) to a flat file.   BCP can also read a flat file and insert into a table.

Oracle has a similar utility called the SQL Loader.

Another possibility is that the target table has some indexes which could be dropped and recreated after the load is complete.

All this being said you really have to identify what is causing your performance problem.  This tip essentially does a select from a SQL Server table and an insert into an Oracle table.

 

Tuesday, January 22, 2013 - 9:46:55 AM - rafal Back To Top (21618)

Hi,

I have serious performance problem with this task. is there any method to speed up the process?

Thursday, October 25, 2012 - 10:58:33 AM - Arif Back To Top (20090)

You have to enlist the help of the Oracle dba to modify the tsnnames.ora so that it points to the oracle database. I find that oracle can store dates that sql server cant so I have to change these character datatype in order for the import to sql server to work. Otherwise its very straightforward.

Saturday, August 4, 2012 - 6:31:38 AM - Dinesh Dattatray Vishe Back To Top (18920)

If I want transfer only mssql view into on oracle system and both are production datbase.What will be best way ? Please give me soltuion.

 

Saturday, July 7, 2012 - 7:49:36 AM - Dinesh Vishe Back To Top (18386)

If both database is online then we do transfer using SSis. already there is some data in oracle then what precation should be talen ??

Friday, March 9, 2012 - 1:49:18 PM - Ray Barley Back To Top (16324)

This tip showed copying from SQL Server to Oracle using the Import and Export Data wizard.  To go from Oracle to SQL Server you just reverse the above; Oracle is your data source, SQL Server is your destination.  You need to get the Data Source name from the Oracle TNSNAMES.ORA file for your Oracle instance (this is also shown above) and you may need an Oracle DBA to actually give it to you.

Friday, March 9, 2012 - 1:15:34 PM - Thanh Nguyen Back To Top (16323)
I need the instruction how to copy the data from Oracle database 11.2.0.2 to SQL Servers 2005.

Monday, November 15, 2010 - 3:56:02 PM - Ray Barley Back To Top (10362)

Have you successfully connected to your oracle database using SQLPlus?  That would verify that you have a good install of the Oracle client. 

Your best bet would be to search the Oracle forums; e.g. a good starting point would be http://forums.oracle.com/forums/category.jspa?categoryID=44 which is the category Windows and .NET.

I think the most up to date Oracle data access from Windows is Oracle's ODP.NET; that's what I've been using, it's Oracle's .NET data provider and is included in the Oracle client (this started with the Oracle 10 client I think; it used to be a separate installer).  You could try using ODP.NET instead of Oracle's OLE DB; look for Oracle Data Provider for .Net when you specify the Oracle destination in the import and export wizard.

Monday, November 15, 2010 - 2:52:00 PM - Shujiku Back To Top (10361)

Thank you for your reply.

Now I have installed Oracle 11g for windows 2008, both 32 bit and 64 bit (win32_11gR1_database_111070.zip and win64_11gR1_database_111070.zip from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html) into D:\Oracle\product\11.1.0\client4win2008_32 and D:\Oracle\product\11.1.0\client4win2008_64.  I have only selected features under "Oracle Windows Interfaces" which include Oracle OLE DB provider and Oracle's .Net data provider.  Created the TNSNAMES.ORA file under both D:\Oracle\product\11.1.0\client4win2008_32\NETWORK\ADMIN and D:\Oracle\product\11.1.0\client4win2008_64\NETWORK\ADMIN.

I have also modified reg keys: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI as following:
OracleSqlLib = orasql11.dll   (old: SQLLib80.dll)
OracleXaLib = oraclient11.dll  (old: xa80.dll) 

When I test connection from either BID or "Import and Export Wizard" selecting Oracal OLE DB Provider I got "Test connection failed because of an error in initializing provider, ORA-12170: TNS: Connect timeout occured". When run TNSPING I got "Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage 3512 not found; No message file for product=NETWORK, facility=TNSAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xxx.xxx.xxx))) TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNSse".  It seems to read the TNSNAMES.ora correctly though.

I have tried to set %ORACLE_HOME% to D:\Oracle\product\11.1.0\client4win2008_32 and D:\Oracle\product\11.1.0\client4win2008_64, but made no difference. The universal installer points to D:\Oracle\product\11.1.0\client4win2008_64.

Not sure what I have missed and would like to find out when I have both 32 and 64 bit installed, what should %ORACLE_HOME% set to, 32 or 64 bit?

Thank you.

Thursday, November 11, 2010 - 6:44:11 AM - Ray Barley Back To Top (10356)

Do you have the Oracle client installed?  The Microsoft OLE DB Provider for Oracle requires it.  Do you have the TNSNAMES.ORA file setup and pointing to your Oracle database?

I would stay away from the Microsoft OLE DB Provider for Oracle.  A while ago (maybe 6 months ago) Microsoft quietly announced on a blog post that they were no longer making any enhancements to it and/or supporting it; I don't remember exactly hw they said it.  I've been on an Oracle project for the last 9 months and the Oracle client works very well running on Windows.

Installing the Oracle client will get you Oracle's OLE DB provider plus ODP.NET which is Oracle's .NET Data Provider.

Wednesday, November 10, 2010 - 6:13:26 PM - Shujiku Back To Top (10354)

How about running Import and Export wizard with Microsoft OLE DB Provider for Oracle?  I have tried it with SQL 2008 on 64 bit Windows 2008 box. When testing connection I got "Test connection failed because of an error in initializing provider.  Oracle client and networking components were not found. ..."  Any Suggestions?

Thanks.

 














get free sql tips
agree to terms