The given key was not present in the dictionary

[BruceTOGAF2]

This is related to my other post 'Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors.'   That error is caused by missing constraints and indexes.

I am trying to migrate Sparx from Oracle to SQL Server.

Due to problems with Sparx Project Transfer Tool, we are keen to use Microsoft Sql Server Migration Assistant (SSMA) which we have installed on the same machine as the target SQL Server DB.

There are various stages in the SSMA process. SSMA stage 'Convert Schema' leads to the destruction of objects previously created by Sparx script EASchema_1220_SQLServer.sql. After the execution of Sparx scripts, Microsoft SSMA is altering some objects, creating new objects and deleting more than 140 objects in SQL Server.

In SSMA I have experimented with avoiding SSMA stage 'Convert Schema'. If I jump straight to SSMA stage 'Migrate Data' I get SSMA error 'The given key was not present in the dictionary'.

Does anyone know how to bypass SSMA stage 'Convert Schema' and avoid SSMA 'Migrate Data' error 'The given key was not present in the dictionary'?

[Geert Bellekens]

It might be faster to write the bulk of the migration code yourself at this point.
As said before, it's nothing more then copying the data table by table, row by row.

A good database specialist or developer should be able to whip something up in a day or so I guess.

Geert

[BruceTOGAF2]

Hi Geert,

I discussed your idea with our Oracle/SQL Server database specialist/developer. He is not able to whip something up in a day or so.

What programming language would be the likely candidate for writing a script that transfers tables from Oracle to SQL Server?

[Geert Bellekens]

Hi Geert,

I discussed your idea with our Oracle/SQL Server database specialist/developer. He is not able to whip something up in a day or so.

What programming language would be the likely candidate for writing a script that transfers tables from Oracle to SQL Server?

Anything I guess, whatever the developer is used to.
If I needed to write something like that it would probably be C# or vbscript with some parts in plain SQL.

I think, if I was tasked with something like that I would do it in two parts:
- write an export for every table in Oracle to a .csv file -> this could probably be written in plain SQL
- Write an import routine to import the data from the .csv file into SQL server. I might write a small C# program, or a VBScript, or maybe even an SSIS package to import the data. A C# program would be as simple as read a csv line, construct an insert SQL statement, execute statement, rinse and repeat. If that is too slow you might even be able to use SQL server Bulk Insert (which is super fast), but then you'll have to make sure the .csv file has exactly the format as expected by SQL server.

for the import, before starting the import you would have to disable all constraints (and enable them again after the import has finished)
I think most of the data would be easy to migrate, but for some fields you might need to transform the data a bit to make them fit in SQL Server. (that could be things like datetime formats, boolean formats (TRUE/FALSE vs 0/1) etc...)

Geert

[BruceTOGAF2]

- write an export for every table in Oracle to a .csv file -> this could probably be written in plain SQL

Many Sparx tables contain BLOBS and CLOBS which CSV exports cannot handle.

[Geert Bellekens]

- write an export for every table in Oracle to a .csv file -> this could probably be written in plain SQL

Many Sparx tables contain BLOBS and CLOBS which CSV exports cannot handle.

I think there are solutions for that (might be using base64 encoding or somehting similar)

Anyway, if that really becomes an issue I guess you could write a program that gets the data from Oracle (in memory) and inserts it into SQL server directly without needed the .csv intermediate format.

Geert

[BruceTOGAF2]

The reason for migrating from Oracle to SQL Server is to improve Sparx performance in a WAN Architecture. I have successfully migrated to SQL Server by locating Sparx Project Transfer on same machine as SQL Server DB server. I was given this recommendation by Sparx Systems Support in April, but my company told me that was not permitted. Having tried other avenues and failed, I was given permission to temporarily locate Sparx EA on same VM as DB server. Instead of Sparx Project Transfer taking 5 days, it took 5 hours.  Our Sparx Production Repository is now on SQL Server. Performance is much better.