Problems while migrating data from oracle to sql server 2008 R2

Hi, In my current job, we are migrating from oracle to sql server 2008R2. We have migrated everything(all the database object) using SSMA except the data. Here, i am having some strange problems. I have fixed the problems which gave me errors. but the following problems do not give any errors while executing:

I have 122 tables in oracle in that particular database. We are migrating 104 of those tables. From some of the tables i am not getting all the data in sqlserver. But, what i found strange is, in my package it does not say that it is leaving some data or deleting. For example, inside the data flow task at the source it says it has 2000 rows coming out, it has 2000 rows coming out from the data conversion and it is putting 2000 rows in the sql server desttination. But i have 2120 rows in the actual oracle table. In the ssis "oledb source", its not even taking 2120 rows; instead its taking 2000 rows. So, where are those 120 rows?

It would have made some sense, If in the ssis package it would have said that its taking 2120 rows and converting 2000 rows into sqlserver, and leaving 120 rows due to a conflict of constraint or somthing like that. But at the source its taking only 2000 rows, so why its leaving the 120 rows from the Oracle database?

In very few tables, the package is not giving any error, but its not getting any data from the oracle at the oledb source and not converting anything into destination. There is no problem with the connection manager for sure. My question is if the source in the ssis package cant reach out to the oracle table, it should give an error. But when i execute, everything turns green but nothing comimg out of the oledb source. But there is lot of data in the oracle table. When i set up the oledb source, i can see the oracle table and its column. So, why there is nothing commng out of the source? And if there is problem connecting to the table then why its not throwing an error?

asked Mar 06 '12 at 03:10 AM in Default

tanzi
-3 ● 2 ● 3 ● 4

(comments are locked)

1 answer:

I can elaborate on the first question. One of the possible reasons could be the varchar2 column participating in the PK. I just finished struggling with this situation myself when the table was created in SQL Server just fine but the migration failed because there was a PK on the varchar2 column with values differing only by case or by a trailing space, for example 'VALUE1', 'value1' and 'value 1 ' are identical to SQL Server but are not to Oracle. To cure the problem it took to change the suspect's column collation to case sensitive but to cure the trailing space was not trivial because the string values differing only by trailing space(s) must be considered equal per ANSI standards. Sadly, Oracle is not compliant with this one.

Also be on the lookout for things like out of range datetime values. In Oracle, dates go well before the January 1st 1753 (the minimum recognized by the SQL Server), so you then need to tweak the project (or the table migration) properties to instruct the engine what to do with those values which appear invalid to SQL Server (or simply map to datetime2).

Another possibility is the foreign keys type mismatch. In Oracle, it is perfectly fine to define a parent table's columns say as NUMBER(15, 0) and then define a child table's column as simply NUMBER (due to sloppiness or oversight). Oracle still creates the foreign key for those but SQL Sever cannot bacause they are different data types.

Oleg

answered Mar 06 '12 at 04:35 AM