Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite

Question

Our old product supports 4 different database types for storing data:

• SQLite
• MySql
• Sql Server CE
• Sql Server

There is also an export feature, which allows to export some data and here is the problem - it actually exports the database itself:

• SQLite and Sql Server CE - the database file (*.db3 or *.sdf respectively)
• Sql Server - the database backup file
• MySql - the whole database directory

Stupid, right? We also came to think so. So, I had to write a tool, which given an old export file converts it to some database independent binary format.

The problem is how to unit test it? At the very least, I need 4 databases with identical data, which brings me to my question:

Is there a tool to reliably convert between the 4 databases? 

Note, that I do not need a super duper tool, which knows to do all the pairwise conversions.

Until now I used http://sqlite2ce.codeplex.com/ to convert SQLite to Sql Server CE, but besides having had to fix several bugs in their code, there is a deeper issue with their conversion - all the integral types become 64 bits long after the conversion, which is not good for me.

Thanks.

Answer 1

You may be able to use Kettle, aka Pentaho Data Integration, to do this.

If not supported natively, someone will have written a plug in for SQL Server CE/SQLLite. Kettle supports any JDBC compliant data source. MySQL and SQL Server are supported of course