CODEWITHSUNDEEP
mysqlsql-server-2005sqlclr
xkingpin
xkingpin

Reputation: 641

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

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)

Upvotes: 0

Views: 165

Answers (3)

Harper Shelby
Harper Shelby

Reputation: 16583

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.

Upvotes: 1

Fahad
Fahad

Reputation: 1271

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

Upvotes: 0

adopilot
adopilot

Reputation: 4510

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

Upvotes: 0

Related Questions