locked
SSMA fails to migrate data due to lost connection RRS feed

  • Question

  • I'm using SSMA for Oracle to load Oracle data onto a SQL2005 server. SSMA runs on the machine that hosts the target database instance. The database has one very large table (only 92K rows, but most rows contain multiple XML-objects). On two consecutive runs the migration of this table was aborted with a messages stating:

     

    DataMigration Error.
    From : "ICORP61"."CONS_INVOICE"
    To : [ICORP61].[dbo].[CONS_INVOICE]

    Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.

    this error message suggests that connectivity to the local SQL Server instance has been lost. Strangest of all is that my first attempt a few weeks ago never had this issue. I'm running SSMA for Oracle V5.2.1259

    I searched the web and did find a comparable issue, dated Tuesday, February 14, 2012 1:59 AM. What can I do to avoid this problem?

    Thursday, November 22, 2012 10:36 AM

Answers

  • Hello,

    thanks for the suggestion. A different client is not needed at all.

    I was able to resolve the issue as follows:

    1) Create 8 tables with the same set of columns as the problem table

    2) Insert from Select to copy the data from the problem table into the 8 identical copies. This gives me 8 large but manageable tables. I chose to separate the data by year, any other selection criterion would have done as well. As long as no rows are left out or copied more than once.

    3) Migrate the smaller tables

    4) Insert from Select in the SQL environment to re-create the complete table in the target environment.

    That's all! Wish you all success with your migrations.

    Abe Kornelis.

    • Marked as answer by Abe Kornelis Tuesday, November 27, 2012 9:16 AM
    Tuesday, November 27, 2012 9:15 AM

All replies

  • Hello,

    Could you try installing the Oracle Client 11g R2 client? Point the ORACLE_HOME to that client.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, November 23, 2012 1:29 AM
  • Hello ,

    I have the same problem when i try to migrate data from sybase to sql server 2008 R2. The table has more than 21000000 rows.

    Could you help please.

    Thank you

    Monday, November 26, 2012 12:38 PM
  • Hello,

    thanks for the suggestion. A different client is not needed at all.

    I was able to resolve the issue as follows:

    1) Create 8 tables with the same set of columns as the problem table

    2) Insert from Select to copy the data from the problem table into the 8 identical copies. This gives me 8 large but manageable tables. I chose to separate the data by year, any other selection criterion would have done as well. As long as no rows are left out or copied more than once.

    3) Migrate the smaller tables

    4) Insert from Select in the SQL environment to re-create the complete table in the target environment.

    That's all! Wish you all success with your migrations.

    Abe Kornelis.

    • Marked as answer by Abe Kornelis Tuesday, November 27, 2012 9:16 AM
    Tuesday, November 27, 2012 9:15 AM
  • Thank you! it was very helpful
    Tuesday, November 27, 2012 10:38 AM