Connecting to MySQL from SSIS
Update: See this follow-up post on writing data to MySQL.
I've recently seen a bunch of questions about connecting to MySQL from SSIS, so I thought I'd give it a try. Our connectivity wiki currently doesn't have anything about MySQL on it, but I'll try and get it update with this information.
My overall findings was that while there were quirks, both the ODBC and ADO.Net drivers that I tried worked fine. Both drivers work with the ADO.Net Source (DataReader Source in 2005), and ADO.Net destination (Katmai only). For ease of use and install, I'd recommend using the ADO.Net driver.
The screen shots in this post were taken with an early February CTP Katmai build.
Our connectivity white paper briefly mentions using MySQL's ODBC drivers, so they were the first thing I tried. I didn't so extensive testing, but it looked like both the 5.1 (beta) and 3.51 (release) drivers worked the same when connecting to my MySQL 5.0.45 server.
To use an ODBC connection in an SSIS data flow, create a new ADO.NET Connection and select the "Odbc Data Provider".
The ODBC drivers didn't show on the list of choices in the windows "ODBC Data Source Administrator" dialog (I'm running Vista x64... not sure if I have to do something special to make them show), so I entered the connection string directly instead of using a DSN.
DRIVER={MySQL ODBC 5.1 Driver};SERVER=<host>;DATABASE=mydb;UID=root
DRIVER={MySQL ODBC 3.51 Driver};SERVER=<host>;DATABASE=mydb;UID=root
Once the connection is created, you can pull data from the database using an ADO.Net Source in the data flow (DataReader Source in 2005). Trying to retrieve the tables using the drop down list resulted in an error:
Switching to use a SQL query instead, and that worked just fine. I was able to pull back both the correct metadata, with one small problem - the varchar(50) columns came back with a length of 51. This resulted in some warnings, but the package ran correctly.
I should note that the first time I ran the package, I got the classic 64bit problem -
[ADO NET Source [1]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
I had only installed the 32bit drivers on my machine (it doesn't look like they will let you install both 32bit and 64bit MySQL drivers at the same time). Switching the Run64BitRuntime project setting to False fixed the issue.
The provider was very easy to install. Setting up the connection manager was fairly straightforward, although I had some weirdness when setting the "Persist Security Info" value to true. For some reason it didn't save my login information the first time I hit OK - I had to open it and save it again.
Like we saw with the ODBC drivers, the tables and views didn't show up in the drop down list (I didn't get a chance to debug too deep into it, so I'm not sure if that's a problem on the SSIS side, or something about the metadata being returned by the provider).
I switched it to SQL query mode ("SELECT * from Customers"), and that brought back the metadata correctly.
The ADO.Net Destination worked fine, although I had to type in the table name as the list wasn't auto-populated, and I didn't run into any problems with the simple data set I was using. The transfer speed seemed reasonable as well. Eventually I'd like to benchmark various ADO.Net providers, but I'll leave that as a topic for another post.
----
NOTE: I did this investigation about a month ago, but didn't have time to complete the write up. It looks like Connector/Net 5.2 has been released since then. It has some interesting features (including VS 2008 integration), so I'll give it a try and update the post if anything has changed.
