Lucky me (I thought), I have to convert an
Oracle 8i database with about 400 tables to Sql Server 2005. So, the first thing
I thought, I'll use the Sql Server Migration Assistant (for Oracle) and probably
the (first part of the) migration will become a peace of cake. Gotcha! Read the
story...
The result of a conversion project is usually that the initial Entity
Relationship Diagram (ERD) is (quit) different from the final ERD.
What I usually do in a conversion-project, is split it into 2 parts.
In the first part, I copy the data from the source-datasource 1:1 to the
destination, Sql Server. "1:1", because I do not implement table-definition
changes at this moment; I only foresee data-type compatibility.
In the second part, the data is migrated from the "original" table-structures to
the destination table-structures.
In this post, I am going to write only about part 1.
Microsoft has a tool, named
SSMA (Sql Server Migration Assistant) to do migrations from
other-vendor-databases. I installed SSMA for Oracle Version 2.2.0, build 380,
with extensions.
The tool is very intuitive. I easily migrated all table-definitions with the
default data-type conversions. Browsing through the menus learned that you can
change the defaults for data-type conversions. So up to the data-migration, I
thought. Unfortunately the button to migrate the data did not function wel :-(
I always got error:
"JDBC Error: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid
column name '"NAME"'.
POSSIBLE REASON: Error while migrating data from table ADRES to table
OracleImport.dbo.ADRES. See Log file for more details."
Although "Name" is not a reserved sql server keyword ("names" is an odbc
reserved keyword + future sql server keyword), I doubted and changed the column
name to "Name1", however, analogue error.
So, I send a mail to SSMA product support and read what they answered:
"I suggest using SQL Server Import Data wizard available in Management
Studio. It uses SSIS mechanism, which is able to migrate such columns perfectly."
I was surprised to get this answer from the product support team, so I asked for
the added value of SSMA. Their answer:
"The primary goal of SSMA is migration of Oracle database objects like
tables, constraints, triggers, stored procedures, packages, sequences etc. –
especially where PL/SQL code is involved.
Data migration was added to SSMA because we wanted to incorporate all migration
steps in one tool.
If the task is about the data transfer only, SSIS is the better solution.
However, if your Oracle tables use any constraints, indexes, triggers, I would
prefer converting them all in SSMA, and use SSIS only for the problematic tables
like ADRES."
So far, a positioning of the product and my short-time experience with it.
As I lost already some time and I could not afford to loose more, I decided to
do it "my way".
As I only need to have the table-structures, no indexes, no constraint nor other
objects, I generated a script of all Oracle-tables using Quest Software "Toud
for Oracle 8.6". I changed this script doing a global replace with the
compatible data-types and run it in Sql Server 2005. Hopsakee, there is the new
(empty) database.
Now I have some possibilities to migrate the data:
1) do an Oracle dump to text-files followed by a bcp in or bulk insert into Sql
Server
2) use SSIS
3) use the classic INSERT INTO ... SELECT FROM.
The first I did not prefer because with 400 tables, I feared that a small
mistake in switching a column could make it difficult to discover.
I did not prefer the second just to not loose time in learning SSIS, I will wait
for the
next UG-evening of SQLUG.be
to get the workings of SSIS explained (lazy me - LOL)
Honestly, I feel good with writing T-SQL!
Using the information_schema views, it was quit easily to generate an
insert-script for all tables.
Creating a linked server to Oracle is not difficult either, so there we go ...
The migration went well for 2/3 of the tables, meaning 1/3 was failing. I was
using the "Microsoft OLE DB Provider for Oracle" provider.
Why did a lot of tables fail? After some analyses, Pieter Martens, a colleague
of mine, and I did remark the following behaviour:
- with the "Microsoft OLE DB Provider for Oracle", the SELECT failed on the
Oracle CLOB data-type
- with the "Oracle Provider for OLE DB", the SELECT failed on the Oracle Number
data-type for the fields where no precision has been given.
This was really annoying. How to solve this?
This behaviour seemed to be Oracle bug 3003992. As suggested in an Oracle
article, Pieter installed an Oracle 10g and imported the Oracle 8i database.
Using a linked server with the "Oracle Provider for OLE DB", all tables, except
1 table failed. Great!
Oeps, I forgot almost to say that a setting has to be changed at the
provider-level to make it working: the "Allow inproces" parameter on the
provider had to be set to TRUE.
The last table could be migrated using ... the other one: the "Microsoft OLE DB
Provider for Oracle".
Yes, now we are ready to start with the 2nd part: migrate to the new ERD!