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.
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.
I have a virtual machine with SQL Server 2008 Developer Edition, Oracle 10g, and the Oracle 9i client.
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:
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:
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 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:
Click OK on the Data Link Properties dialog then click Next. You will see the Specify Table Copy or Query dialog as shown below:
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:
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:
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:
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:
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 Finish to execute and save the SSIS package. You will see the following dialog (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:
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.
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.
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
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?
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
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.
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
Tuesday, January 22, 2013 - 11:57:59 AM - Ray Barley
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.
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
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
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.
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?
Thursday, November 11, 2010 - 6:44:11 AM - Ray Barley
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
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?