다음을 통해 공유

Cross-Database Connectivity: An Answer For Kyle

  • 아티클

Kyle Schoonover, a former colleague in MSIT, posed the following questions in a comment on October’s cross-version database attachments post:

Ward, I'm currently working in MySQL and writing conversion scripts to convert a MySQL database to a SQL 2005 database. I have also been using OpenQuery to query through a linked server from SQL 2005 to MySQL. I was wondering if you could comment on a way to possibly use DTS to transfer data from a database that is not readily supported like MySQL.

Also is there something better than using OpenQuery to create dynamic SQL to run against a linked server database that runs on MySQL.

It’s always great to hear from Kyle, who is a very sharp SQL guy.

Rather than using DTS for this purpose, Kyle, I suggest you look into SQL Server Integration Services under SQL Server 2005/SQL Server 2008. It should be a trivial task to build a connection to your mySQL instance (ODBC, if all else fails); you’ll then have access to the full ETL capabilities of SSIS. I’m aware people accessing Oracle and Teradata databases via SSIS using this technique, so I don’t think mySQL should be an issue.

To your second question, if you’re thinking of portable T-SQL code, I think OpenQuery is probably the best way to go.

Great to hear from you, Kyle, and I hope we can hook up next time I’m in the Pacific Northwest!

     -wp

Comments

  • Anonymous
    November 08, 2008
    This is a good answer to the question about converting data from MySQL to SQL Server, and then 5 minutes later I opened up the November issue of SQL Server Magazine to find the article "Integrate MySQL and SQL Servers" by Robert Sheldon which supplies all the details.