3

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.
Improve this question
6
  • 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?
    – mrdenny
    Sep 29, 2011 at 22:59
  • 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
    – gbn
    Sep 30, 2011 at 3:04
  • 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.
    – mrdenny
    Sep 30, 2011 at 7:21
  • @mrdenny - I don't know the switches, but I might be able to find out.
    – Sean
    Sep 30, 2011 at 11:27
  • @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. :)
    – Sean
    Sep 30, 2011 at 11:30

4 Answers 4

Reset to default
1

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.

Improve this answer
4
  • 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.
    – mrdenny
    Sep 30, 2011 at 1:51
  • 1
    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.
    – Deleted
    Sep 30, 2011 at 6:16
  • 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.
    – Sean
    Sep 30, 2011 at 11:36
  • 1
    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).
    – Deleted
    Sep 30, 2011 at 12:46
0

http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_xml

The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

I would go with the XML format then, and convert it wherever necessary using XSLT.

Improve this answer
0

in stackoverflow they pointed to this solution: from mysql to mssql. It requires loading a mysql instance.

If your requirements are set in stone (I am referring here to you comment about "require no manual intervention"), then the best solution for your organization would be to pay someone to program this logic for you. Migrating stuff between databases requires intervention, it will not be done by little garden gnomes while you are sleeping. Someone will have to program the logic of it.

Having written all that, running mysql on a windows server is perfectly fine and will cost you nothing if you run the community edition. You may always burn money buying it from Oracle, by the way if that is a management requirement.

Improve this answer
0

If you dont' want to run a MySQL instance & script the import and re-export (in a MSSQL friendly format) then you'll have to write a parser to translate the MySQL dump format to CSV or something useful & simple.

I think you're better-off running a MySQL instance and swapping in/out the data. You could even use VBA to automate this through a DSN once the data is in MySQL.

Since you probably run a business where your time is worth something, consider a commercial solution like symmetricds

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.