SSMA Oracle to SQL failed but no error

_{Friday, January 4, 2013 9:41 PM}

This is my first attempt using this tool and unfortunately I am not an official DBA, I just play one at work because we have no one else...

Ive installed SSMA 5.2 + Oracle Extension pack and able to connect to both Oracle and SQL server. 

When I attempt Migrate Data for one table it fails without an actual error:
Migrating data...
Analyzing metadata...
Preparing table HARVEST.HARALLCHILDRENPATH...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
Data migration operation has finished.
*0 table(s) successfully migrated. *
*0 table(s) partially migrated. *
1 table(s) failed to migrate.

If I run an Assessment Report on that same table this is what I get:

*    O2SS0362: SQL Server Migration Assistant for Oracle Error message: ON DELETE CASCADE references have multiple cascade paths(2) Estimated manual conversion time: 2 hr(s)1 HARALLCHILDRENPATH(2), Estimated manual conversion time: 2 hr(s)  *

*      O2SS0486: SQL Server Migration Assistant for Oracle Error message: Primary or unique constraint not converted.(1)2 HARALLCHILDRENPATH(1) *

*    O2SS0356: SQL Server Migration Assistant for Oracle Warning message: Conversion from NUMBER datatype can cause data loss(4)3 HARALLCHILDRENPATH(4) *

I have a feeling that this is relatively easy to resolve but my experience ends with writing queries, backups and account management. I was hoping someone would be able to help walk me through these issues so I can successfully migrate the data that I need .
Let me know if you need any more details on my end.

Ive noticed some other who ran into the issue had case-sensitivity problems, but Ive tried multiple combos for the server names of both servers but no luck.

Your help is GREATLY appreciated!!

_{Monday, January 7, 2013 4:35 PM ✅Answered}

Hello,

My suggestion is to disable constraints during maintenance hours, while the table is not been updated. Take in consideration that enabling the constraints on the Oracle table my require some time depending on how big is the table.

Please contact your Oracle DBA to estimate time for this task, and do it during maintenance window.

Another option, is to restore a backup of the Oracle database on a non-production server, and disable the constraints. Perform the upgrade from that server.

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

_{Sunday, January 6, 2013 9:24 PM}

Hello,

Could you try to disable constraints on the HARVEST.HARALLCHILDRENPATH table for the migration process only?

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

_{Monday, January 7, 2013 1:46 PM}

Thanks Alberto, I checked both Project Settings and Default Project Settings (Migration) and both "Check Constraints" are set to False.

Is that the correct place to disable constraints?

thanks!

Lionel.

_{Monday, January 7, 2013 3:54 PM}

Hello,

Please do that on Oracle. Disable constraints on the table.

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

_{Monday, January 7, 2013 3:59 PM}

Hello,

Please do that on Oracle. Disable constraints on the table.

Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

I will have to put in a request to our Oracle DBAs to do so. Is it enough is I just ask "Please disable constraints on HARVEST.HARALLCHILDRENPATH" or should I ask for something more detailed? Also please note this is our Production database we are attempting to migrate from.

thanks!

Lionel.