How to create a SQL Server database from a mysqldump file automatically on a daily basis?

Question

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.

Solution

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.

Comments

[Ryan Ferretti]

Any chance you know the actual switches for mysqldump that vendor will be using? What format are you expecting the mysqldump file to be in?

[gbn]

Did you purchase an app the runs on SQL Server from the vendor? Or are they just a supplier of data? If the latter there my be alternative vendors: I've never heard of data being sent like this

[Ryan Ferretti]

Sadly I've seen this before. Nor from a MySQL vendor but a mainframe vendor. It was their format or nothing, and they reserved the right to change the format at their whim.

[Sean]

@mrdenny - I don't know the switches, but I might be able to find out.

[Sean]

@gbn - Actually, it's a solution hosted by the vendor, so in essence, they are just a supplier of data. But the vendor is very integrated around here, so switching vendors is unfortunately not an option (especially not this late in the game). It's nice to dream about, though. :)

[Ryan Ferretti]

@Sean If you can find out what they are that'll probably make it easier as you'll have a better idea about the data format you have to work with. Do you have a current export to look at?

[Ryan Ferretti]

The OP specifically stated that he can't change the way the dump is being done and that he doesn't want to setup a MySQL Server.

[Deleted]

Yes I did read it. The OP wants the moon on a stick. You can't win them all. Local MySQL is the best option.

[Sean]

That comment felt a little harsh, Chris; trust me, there are so many ways I'd rather do this, but the vendor has us cornered... I'm sure we've all encountered vendors that take the stance "our way or the highway", or at least "our way or the pay way" and charge for "customizations". GAH! I do recognize that bringing up a MySQL instance might be the only solution... we'd just really like to avoid it, so I was hoping that someone else might have found another utility to make it easier. Thanks.

[Deleted]

Sorry if it sounded harsh. Vendors are like that. It's best to play along rather than fight them. The only other option I can see is parse and rewrite the SQL but that is going to take a lot of code and is time consuming and therefore expensive (so much more so than bringing up a MySQL instance).