Best solution to import records from MySQL database to MS SQL (Hourly)

Question

I need to import records stored in a MySQL Database that I do not maintain into my Sql Server 2005 database (x64)

We should import the records at an interval basis (probably 1 hour).

What would be the best solution to perform the regular import?

1. Windows Service (using reference MySql.data dll)
2. Windows Client (could make it automated)
3. SQL Extended Stored Procedure (is it possible to reference the MySQL.data dll?)
4. SSIS package - Install MySQL ODBC driver

The problem with #4 is that I do not really want to support the ODBC driver on the sql server.

I'm not sure if you can even reference the x86 MySql.data dll into a x64 sql server process for #3. (Or if you can even reference that dll within a sql server project)

Answer 1

What abaut MySql -> *.DAT (per table) -> FTP to WINSERVER -> SSIS to SQL

Answer 2

4.SSIS package - Install MySQL ODBC driver would be the best solution.

Answer 3

If it were mine to do, I'd go the SSIS route. Sure, you'll "maintain" the ODBC driver on the server, but compared to the maintenance (and development time and headaches) of the other 3 options, that seems to be the simplest route.