Wednesday, February 08, 2012

Exporting a SQL Server database directly to Oracle

I was faced with the task of importing a SQL Server schema, data and all, over to an Oracle database. Initially I started to go the route of exporting to flat files, then I realized I apparently had to do this one by one for all 30-something tables in the SQL Server database! I looked into a better way...

Assuming an Oracle database is running on one machine, and MS SQL Server with Management Studio on another...

1) Obtain Oracle Data Access Components (ODAC) - http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html - you may need a different link depending on whether you need 32 or 64 bit (or different version?)
2) Install ODAC on the Windows server running SQL Server according to the README.
3) a) Open SQL Server Management Studio. I'm using 2005, so the dialogs may be different for newer versions.
b) Right-click the database you'd like to export, then click Tasks, Export Data.
4) Choose data source FROM which to copy data - probably SQL Native Cilent with SQL Server Authentication...The database selected should be the one you right-clicked earlier.
5) Choose destination. Oracle Provider for OLE DB worked for me. Microsoft OLE DB Provider for Oracle did NOT work. Upon executing the export, this provider immediately spat out a mysterious "Data type is not supported" error.
6) Click Properties. Data Link Properties dialog will come up. There might be multiple way to define "Data Source", but what worked for me was [IP]/[oracle SID]" - for example 10.1.2.3/mydb ...then enter the oracle user/pw and hit Test Connection. When the test is successful, make sure you check off "Allow saving password" because if you don't it will try to log in with empty password and fail. Click OK.
7) Pretty obvious from here. (Hit next a few times, select the tables you want, etc.)