locked
SSMA Oracle to SQL Server data migration step fails with no explanation RRS feed

  • Question

  • The conversion process worked fine and created the objects in SQL Server but when I try and migrate the data it doesn't work and gives no explanation why.  The output looks like this:

    Migrating data...
    Analyzing metadata...
    Preparing table TIMECARD.TBLSTAFFID...
    Preparing table TIMECARD.TBLTIMECARD...
    Preparing table TIMECARD.TBLTIMECARDDETAIL...
    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.
     3 table(s) failed to migrate.

    There are no errors on the error list tab.  The report looks like this:

    Status    From-Table    To-Table    Total-Rows    Migrated-Rows    Success-Rate    Duration(DD:HH:MM:SS:MS)    Error-Messages
    Initial "TIMECARD"."TBLSTAFFID"                                                     N/A  
    Initial "TIMECARD"."TBLTIMECARD"                                                  N/A  
    Initial "TIMECARD"."TBLTIMECARDDETAIL"                                       N/A

    There is a red X for the status on each but when I select any row the details button remains greyed out.

    Same result whether I use server side or client side migration.

    Tuesday, April 24, 2012 10:41 PM

Answers

  • After a lot of building of VM's and reverting snapshots to test a lot of different things it turns out this was a problem with the case sensitivity in the schema mapping.

    The database is named TIMECARD in Oracle and when I have been creating it in SQL Server I have been calling it TimeCard.  Even though it seems to be ok when it creates the objects on the SQL Server, when it goes to migrate the schema mapping defaults to:

    TIMECARD --> TIMECARD.dbo

    This does not work.  But when I change the schema mapping to:

    TIMECARD --> TimeCard.dbo

    Then it works fine.

    For what it is worth I am using the default collation sequence on the SQL Server which is case insensitive so this looks like an SSMA sensitivity.

    I still don't know what the memory error messages in the SSMA log are about when I use the 32 bit version but they don't seem to make any difference as far as I can tell.

    • Marked as answer by MnM Show Thursday, April 26, 2012 11:04 PM
    Thursday, April 26, 2012 11:04 PM

All replies

  • Hi MnM Show,

    Regarding to your description, there is no explanation and no error message. Based on my research, the SSMA program files, you must also install the SSMA for Oracle Extension Pack on the SQL Server.

    For more information, please refer to Migrating Data into SQL Server Using SSMA
     http://blogs.msdn.com/b/ssma/archive/2011/03/30/migrating-data-into-sql-server-using-ssma.aspx

    Meanwhile you can try to create a new project again. Maybe some corrupted objects in the other project.

    For further help please directly contact to the blog of SSMA team.

    Regards, Amber zhang

    Thursday, April 26, 2012 3:34 AM
  • Hi Amber

    Yes I have the extensions on the SQL Server.  I tried creating a new project with same results.  The SSMA log file has been showing a memory error every time I launch SSMA but since everything else had worked up to this point including creating all of the objects in the SQL Server I hadn't worried about it but now I am focusing on that as a possible source of this issue.  I have SSMA on a 64 bit machine, but since my normal Oracle client install is 32 bit and since I have it packaged for an automatic installation I used that.  Which also means that I have been forced to use the 32 bit version of SSMA since it relies on that client to connect to the Oracle database.  Whenever I launch the 64 bit version of SSMA this is added the SSMA log:

    [Generic: Mandatory] [2140/1] [2012-04-26 10:03:18]: SQL Server Migration Assistant for Oracle v5.2.1259

    But each time I launch the 32 bit version it adds all of this to the log:

    [Generic: Error] [2372/1] [2012-04-26 10:03:51]: SQM initialization error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
    [Generic: Error] [2372/1] [2012-04-26 10:03:51]: Exception: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
     site: Void SqmSetMachineId(UInt32, System.Guid)
     source: Microsoft.SSMA.Framework.Generic.Factories
       at Microsoft.SSMA.Framework.Generic.SQM.SqmLibWrapper.SqmSetMachineId(UInt32 hSession, Guid Guid)
       at Microsoft.SSMA.Framework.Generic.SQM.SqmSession.SetMachineId()
       at Microsoft.SSMA.Framework.Generic.SQM.SqmSession..ctor(String name)
       at Microsoft.SSMA.Framework.Generic.SQM.SqmSSMAManager.InitSession()
       at Microsoft.SSMA.Framework.Oracle.SqlServer.SQM.SqmOracleSSMAManager.InitSession()
    [Generic: Mandatory] [2372/1] [2012-04-26 10:03:52]: SQL Server Migration Assistant for Oracle v5.2.1259

    But as I said, everything still seemed to work despite this error in the SSMA log up until the migration itself.  I did research this error when I first noticed it but the only reference I could find suggested it was a .NET issue, however that was in regard to .NET 2 and the SSMA client is on a Windows 7 machine so .NET 3.5 is native.  Just for the heck of it I did try it also with .NET 4 installed and it did the same thing.

    I think I will try putting the 64 bit client for Oracle on the SSMA machine and try using the 64 bit version of SSMA and see what happens.

    Meanwhile I am also setting up a Windows XP machine to run the SSMA client as a test as well.

    • Edited by MnM Show Thursday, April 26, 2012 4:18 PM
    Thursday, April 26, 2012 4:12 PM
  • No luck using the 64 bit version.  Identical behavior when I try and do the migration.  No memory error messages in the SSMA log.

    Thursday, April 26, 2012 6:32 PM
  • After a lot of building of VM's and reverting snapshots to test a lot of different things it turns out this was a problem with the case sensitivity in the schema mapping.

    The database is named TIMECARD in Oracle and when I have been creating it in SQL Server I have been calling it TimeCard.  Even though it seems to be ok when it creates the objects on the SQL Server, when it goes to migrate the schema mapping defaults to:

    TIMECARD --> TIMECARD.dbo

    This does not work.  But when I change the schema mapping to:

    TIMECARD --> TimeCard.dbo

    Then it works fine.

    For what it is worth I am using the default collation sequence on the SQL Server which is case insensitive so this looks like an SSMA sensitivity.

    I still don't know what the memory error messages in the SSMA log are about when I use the 32 bit version but they don't seem to make any difference as far as I can tell.

    • Marked as answer by MnM Show Thursday, April 26, 2012 11:04 PM
    Thursday, April 26, 2012 11:04 PM
  • Thanks big man, you solved my problem.
    Tuesday, March 29, 2016 2:50 PM
  • I had a similar problem - data migration failed with status of "Initial" on each table.  In my case, my SSMA project originally migrated to a database called Kentest which was case insensitive.  When I ran into problems with duplicate keys (because Oracle is case sensitive), I created a new database called KEN_CASE which is case sensitive.  The initial migration to this database ran cleanly.

    Thinking I didn't need the original Kentest database, I took it offline.  Somehow the migration assistant project still contains references to this database which caused the migration to fail.  Brining the Kentest database online allowed the migration of data to KEN_CASE to run cleanly. 

    I found the problem by going into Tools > Global Settings > Logging and changing the message level for [All categories] to "Info" (the original value is "Warning").

    Ken

    Wednesday, November 23, 2016 3:55 PM