Importing data from Oracle database to SQL Server using SSIS

Let’s say we have a couple of tables in Oracle that need to be imported to SQL Server. Note that only certain tables need to be imported, and not the entire schema (packages, functions, etc).

One of the easiest ways to do this is to import using SSIS. Here, I will list the steps taken to import from Oracle 11g to SQL Server 2012 database using SSIS installed with SQL Server 2008 R2.

Launch BIDS and create a new Integration Services Project. Create two new Connections in the Connection Manager. One of them should connect to Oracle. I used “Microsoft OLE DB Provider for Oracle”. Your Oracle connection should look like this :

Oracle_CM

As you can see above, you need to provide the correct Server name (Oracle database instance) and your username/password to connect to the Oracle schema.

Next, you need to create a SQL Server connection. I used the “SQL Server Native Client 10.0” provider for this. Here is a screenshot :

SQLServer_CM.jpg

So again, provide the instance detail (including the port number) in ServerName, and choose either Windows or SQL Server authentication to connect to the server. Once authenticated, the databases drop-down will be populated, and you can choose your SQL Server database to import the Oracle tables to.

Now let’s import the data. Drag a new Data Flow Task into the Control Flow pane. Double click to get to the Data Flow pane.

ControlFlow_pane.JPG

DataFlow_pane.jpg

Your “OLE DB Source” is the Oracle database, so double click it and assign the Oracle connection that you created earlier. Once it connects to the Oracle db, it will load all the tables and views in the table drop-down. Choose the table that you want to import. This may take a while if there are a lot of Oracle schemas on the source database. Now it should look like this:

Oracle_01.jpg

Now double click the “OLEDB Destination”. This is your SQL Server target database. Provide the SQL Server connection that you gave earlier. You can choose to create a new table to import the data to :

SQL_01.jpg

In general, you dont need to change any default values.

You need two more steps to complete the process:

  1. Assign AlwaysUseDefaultCodePage=TRUE in the Source properties :

DefCodePage.jpg

2. Set “Run64BitRuntime” to False in the main Project Properties pane. Apparently, this is because there is some issue in the 32-bit drivers vs the 64-bit drivers. You can check more here : https://blogs.msdn.microsoft.com/amitlale/2007/12/11/ssis-64-bit-setting/

Run64.jpg

That’s it. Now run the package and it should seamlessly import the Oracle tables to your SQL Server database.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s