Mysql – How to create a SQL Server database from a thesqldump file automatically on a daily basis

automationmigrationMySQLsql server

Recently, a vendor announced that they were upgrading their processes, and an automated, daily data dump we are getting from them will now come in a MySQL datadump format. Unfortunately, we're a Microsoft-only shop, so we can't import those files directly.

So my question is: How can I take the data dump file and import it into a SQL Server 2005 instance to create a database?

Things to note:

  • The solution needs to be automated and require no manual intervention; it has to be something we can schedule daily and just get notification that it worked / failed.
  • We'd love tools that are free (who doesn't?) / already purchased (e.g., already a part of SQL Server), but we're okay with buying something if needed.
  • We have no control over the data or the original database, so we don't have control over running the mysqldump command again with the 'compatible' option set differently.
  • We really, really don't want to do something like load up a local instance of MySQL to import the dump and then pump the data over to MSSQL. Like I mentioned, we're an MS-only shop, and we're not set up to support a MySQL instance, even for an automated process like this.

Best Answer

I would do something different with the 3rd party. I would pull it from MySQL directly into SQL Server using SSIS and schedule the task inside SQL server. Importing a MySQL dump into SQL Server would be problematic as the DDL is significantly different between database engines.

If you can't access the 3rd party directly, you can get a running MySQL instance locally then import the mysql dump (using mysqlimport in a batch file).

You should be able to automate this easy enough with task scheduler and SSIS + Jobs in SQL Server 2005.

Related Topic