Error:Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Category: sql server migration
island82 on Tue, 22 Jan 2013 19:55:31
We are currently using SSMA v5.2 to migrate from Oracle 10.2.04 to SQL 2008R2, we have a few tables that has very large row counts, one is 400million rows, another is around 30million rows (we have not selected to convert this 2 tables in the POC) and the
one that caused the error on the topic is 300k rows but contains blob. Another table that contains blob but only about 30k rows were migrated fine if we selected it by itself, it took around 1hr for that particular table. With that in mind we thought that
perhaps the table with 300k blob rows may be ok if selected by itself, but it threw the error around 25%
Is SSMA able to handle large blob migration for this case and are there any settings that can be tweaked to improve the migration. We are concern on the tables that have millions of rows if SSMA is failing on BLOB migration. Would appreciate any pointers/help/suggestions.
Alberto Morillo on Wed, 23 Jan 2013 11:34:22
Use SQL Server Integration Services (SSIS) to create a SSIS package and import that BLOB data. Try using the .NET Framework Data Provider for Oracle on the SSIS package to get connected to the Oracle database.
You can also ask the SSMA product team directly for product support. You can contact them via ssmahelp@Microsoft.com
Hope this helps.
island82 on Fri, 15 Mar 2013 01:30:35
Thanks! I'll shoot an email to the SSMA product team!