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 :
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 :
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.
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:
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 :
In general, you dont need to change any default values.
You need two more steps to complete the process:
- Assign AlwaysUseDefaultCodePage=TRUE in the Source properties :
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/
That’s it. Now run the package and it should seamlessly import the Oracle tables to your SQL Server database.