Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Export SQL Server Data to Oracle using SSIS

MSSQLTips author Ray Barley By:   |   Read Comments (15)   |   Related Tips: 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.


Last Update: 5/18/2010


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, April 12, 2014 - 10:17:48 PM - rajesh Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 04, 2012 - 6:31:38 AM - Dinesh Dattatray Vishe Read The Tip

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 07, 2012 - 7:49:36 AM - Dinesh Vishe Read The Tip

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 09, 2012 - 1:49:18 PM - Ray Barley Read The Tip

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 09, 2012 - 1:15:34 PM - Thanh Nguyen Read The Tip
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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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.

 




 
Sponsor Information