Archive for the ‘Data Migration and ETL’ Category

Data migration from Oracle to SQL Server with SQL Server Management Studio (SSMS)

October 4, 2012 Leave a comment

It all depends on the goal. You may be required to transfer all the data from all the tables on Oracle DB side to SQL Server, or just some of those tables or just certain data, i.e. applying filtering. If you encounter such a task for the entire database (AKA Schema in Oracle), especially with filtering, I would say go with any ETL Tool you have in hand: SSIS, Oracle Warehouse Builder,  Cognos Data Manager, Data Services of Business Objects XI, with Informatica and DataStage among the industry biggest giants. These are all powerful, enterprise ETL solutions. There are quite a few less well-known, yet too powerful Open Source  ETL Solutions like TalendPentaho, CloverETL.

The former two one, – Pentaho and Clover, do declares themselves Open Source, yet the same time you can’t just download neither of them for free, apart from trial versions. At least, I couldn’t fine a link to freely downloadable, full-featured installation package. Not sure what kind of Open Source those Pentaho and Clover are than. That’s why listed them as commercial ETL tools. It’s only Talend, as for the current date, doesn’t charge anything, being the only true Open Source among those three.

Anyhow, back on the track. If you don’t have any of those tools, including SSIS, which you really should have just because it comes with SQL Server installation, but by whatever reason it’s not available (like in my case where I can’t have anything but SSMS working on my 32-bit Win7, installed on x64 laptop… don’t ask why), so you can count only at SSMS as the only major tool which has the data import/export, this post might be just what you need.

The idea is simple: get table(s) DDL(s) and use it while setting up data import from Oracle with SSMS. In order to do that, you would have to have Oracle Client in place. Another trick links to SQL Server 2008 Enterprise / 2008R2 Enterprise Data Compression feature. Please, refer to my post on Data Compression for details and a reason why I believe Data Compression shall be used at least in this kind of scenario.

Chapter 1. Downloading and Setting up Oracle Client

Intended for those poor soles who never worked with Oracle and don’t have a clue of what it is and how to approach it even from the point of how to install the clients. Any others of you, at least to a certain degree Oracloids or especially Oracle DBAs, please roll forward to the next chapter as you definitely wouldn’t find anything useful for yourself in this one.

1.1  Download the media from Oracle Downloads.

I assume, Oracle’s marketing people made the decision of removing links to any previous Oracle version from the Downloads page. Luckily enough, it’s Oracle’s techies who’s made current version Oracle Client perfectly compatible with any previous ones. Well, “perfectly compatible” is a bit of exaggeration, bit it does work fine for the total majority of time, leaving users with only few scenarios and moments here and there when Oracle 11gR2 client, which is the current AND THE ONLY AVAILABLE FOR DOWNLOAD FROM ORACLE WEBSITE, would cause troubles when working with Oracle Servers of previous version. (using exp/imp might sometimes, – don’t remember those minor hiccups by now, but they are easily workaround-able anyway) In any case, Oracle 11gR2 client would be totally fine to use for the purpose of data migration with SSMS in general and this post’s context in particular.

There is an alternative of going with so called Instant Client, but working with it is full of surprises. I don’t stand Oracle Instant Client and don’t advise you on using it because it will fail here and there all the time making you constantly googling for just problem solution. I highly encourage you to download and work with the full client.

– Click on “Accept License Agreement” – it’s important. Not the license agreement itself or reading it, of course.  It’s the radio button – if you don’t click on it, the download won’t start.

– Choose the right installation for your platform and click “See All”

– Accept the license agreement once again

– Click on Client link

– The download would start. Wait for it to finish, unzip and run setup.exe

1.2  Setting up Oracle Client

When the setup is done, a new dialog of Oracle Net Configuration Assistant  is being popped up, but more often than not it doesn’t come on top of the installation window. To make it appear, either minimize all the opened windows one by one, or simply click on Windows’ Show Desktop icon. This Net Configuration Assistant window would remain the only one on your screen.

Chapter 2.  Get the table(s) DDL(s)

1.1  Run SQL*Plus

First, let’s alter SQL*Plus shortcut a bit

Now, run the executable, connect with credentials of the schema you want to get table(s) DDL(s) from. Otherwise, if you have your own user, you would need to either make sure you have SELECT_CATALOG_ROLE role or be granted one.

Type or copy/paste this code:

set echo off;
set heading off;
Set pages 1000;
set long 5000;

select dbms_metadata.get_ddl(‘TABLE’, ‘TRAN_STATS_SRI_DLY’, ‘DATAW’) from dual;

Copy the result and paste in SSMS query editor.

Start with replacing double quotes with empty strings – only if you feel this way, as it’s not required. SQL Server accepts double quotes and square brackets in tables and columns naming conventions

Replace “NUMBER” with “NUMERIC”

Then replace VARCHAR2 with VARCHAR

Finally, replace any non-SQL Server keywords, such as “ENABLE” in this example

Comment out the bottom part of table’s DDL, so that it doesn’t include constraints and storage settings


and that’s it – you have the right table DDL to use on SQL Server-side when creating a table to host data pooled over from Oracle.

Chapter 3.  Importing data

Create SQL Server schema of the same name as Oracle schema which owes the table to be imported.


(Actually you can create schema of any name or choose not to create schema at all, – this way the imported table would be created under the default dbo schema, but this kills he consistency and clarity as the result)

Right click on the to-be-data-destination database -> Tasks -> Import Data

Here you can go by a number of different ways. Normally, to fascilitate “better”  performance in data import from Oracle to SQL Server, Oracle Data Access Components (ODAC) should be installed, – this way  Oracle Data Provider for .Net (ODP.Net) could be used instead of MS .Net Framework Data Provider for Oracle. But if you have installed Oracle Client 10gR2 then you will have to install ODAC 10gR2 or ODAC, which can’t be installed on Windows 7, for example. Long story short – it’s not always straight forward with ODAC. On the other hand, ODAC is not really needed. I mean Oracle claims it to be very fast and superb over any Microsoft Data Providers, but if it takes to scratch your left year with your right arm and that by pooling it over all the way behind your back… heck with ODAC! Microsoft .Net Framework Data Provider for Oracle is indeed quite slow, but Microsoft OLE DB Provider for Oracle is really quite fast. Fast enough to be used out of the box without having to go through the mess with installing ODAC.

Destination – should come up itself (because the Data Import wizard was ran with cursor on the destination db)

Write a query to specify the data to transfer

Key in your select * from <source_table_name> statement

Put in the full destination table name, i.e. including earlier created schema and hit “Edit Mappings”

Click “Edit SQL”

Delete everything (RMB + “Select All” + Del. Ctrl+A won’t work here) and then paste earlier prepared table DDL (Chapter 2)




Hit OK, ignore any warning messages

OK once more, Next, change Fail to Ignore under OnError(Global) and OnTruncation(Global)

Next, leave “Run Immediately” checkbox checked and click Finish.

Sit back and enjoy