Convert a MySql database to MS SQL Server 2005 with data?

Question

I'm trying to create a completely new database from an existing MySQL database, bringing over both data and schema, but so far the only way I've been able to do this is to first import the MySQL database into MS Access, and then into SQL Server 2005? Crazy right? Surely, there is a way that doesn't involve a tedious, custom time-consuming programming, right (perhaps using SSIS)?

A few additions to my original description above:

• Its a pretty good size database (easily a few gigs).
• I'm working in an MS environment (asp.net, C#)
• I'm under a tight deadline so I'm looking for an automated process that requires little to no effort in the conversion process.
• SSIS would be the preferred way via BIDS (VS 2005)

Thanks for all the great input!

Answer 1

I believe that using the phpMyAdmin tool you can script the MySQL database structure and data into a sql script. Then you simply run those two scripts on your SQL Server 2005 database and it should, in most cases, create the database and fill it with data. It's been a couple years since I had to do it myself, but as I recall that was the process I used to transfer a MySQL database to SQL Server in the past. You will probably have to alter the structure script to change some of the data types to their SQL Server equivalents, but the data should load just fine once you've got the data types all sorted.

Answer 2

I think you can use SQLYog to generate some fairly standard SQL which will dump out and recreate your db, with data. You may have to massage its output for SQL Server's dialect of SQL a bit, though...

Answer 3

The responses I received were certainly helpful, but the solution it would seem is to do a mysqldump and then run that script from SSIS, massaging the output as needed; however, AFAIK it is not possible to use VS 2005 BIDS to create an SSIS package that completely transfers a MySQL database to a SQL Server 2005 database (data and schema) using Windows Vista 64. I said AFAIK, but who knows the interwebs have much to reveal :)