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:
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?
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, 2012 at 04:35 AM