Problem migrating large table with SQL Server Migration Assistant for Oracle

Question

Hi

I'm trying to migrate a table with approximately 850 000 rows containing documents stored in a long raw field. The conversion is set to store those as Image-fields in MS SQL. But after 400 000 rows I get a Data migration error:

Could not count rows in the target table after migration. Migration may have succeeded, but row count in not verified.

Error: ExecuteReader requires an open and available Connection. The connection's current state is closed.

ExecuteNonQuery rewuires an open and available Connection. The connection's current state is closed.

I have tried with these changes under Migration in Default Project Settings:

Batch Size: 2000 (also tried 1000)

Data migration timeout: 1440

In my latest test I also tried with Table lock set to No.

Any help would be appreciated.

Regards, Torbjörn

Answer 1

Hi TorbjörnLilja,

>> ExecuteReader requires an open and available Connection. The connection's current state is closed.

There should be something like query timeout (or command timeout) and connection timeout, please make command timeout as 0 and try.

Besides, as a workaround, we can also separate the table and migrate the data 300000 rows one time.

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi,

Seems like SSMA gets disconnected from source or target. I remember seeing this with Oracle, where it would run out of some temporary space to serve query results, if dataset was big. In your case, since it's a binary data, I think this might be what's happening.

You can migrate table in chunks, by providing custom select query. To do so, navigate to Tools -> Project Settings -> General -> Migration and switch "Extended data migration options" to "Show". Then when you select your table in the source objects tree, there will be a new tab "Data Migration Settings". On that tab you can disable table truncation and provide custom SELECT query to migrate data in chunks.

Regards,
Alex.

Answer 3

I found the issue with the disconnects and SSMA. It is simply the default query timeout of 15 min set in Tools, Project settings.

Change this to a large number like 200min or more and all the data converts without a disconnect.  genius, if the error msg only told us it was due to a timeout.