Connecting to MySQL from SSIS

Connecting to MySQL from SSIS

  • Comments 19

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.

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.

ODBC - Connector/ODBC 5.1, Connector/ODBC 3.51

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".

image

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

image

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:

image

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.

ADO.NET - Connector/Net 5.1

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.

image

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).

image

I switched it to SQL query mode ("SELECT * from Customers"), and that brought back the metadata correctly.

image

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.

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • Am I the only one that can't get this working? I see loads and loads of other folks that can't seem to write data to MySQL. I can see the schema of the MySQL DB (I am using .NET Connector 5.2.5) but cannot write even a single row to the destination DB.

    Did you test an Insert or Update against a MySQL database? If so, what is the sql_mode of your MySQL DB?

  • A couple of users reported being unable to use the ADO.NET destination to insert data into their mysql

  • Hi,

    I am unable to connect My SQL server by following above mention steps in blog. Can u provide me more step or sample SSIS package to transfer data from SQL TO MY-SQL Server.

    currently I am unable to get ADO.Net Source code Editor.

    Please Help Me.

    Thank You

    Vaibhav

  • Hello,

    Are you using SQL 2005 or SQL 2008?

    The ADO.NET Source was added in 2008. 2005 has something similar - the DataReader Source, but it doesn't have the same UI that I show in this blog.

    ~Matt

  • Hi Matt,

    I am getting following error message in SQL 2008 while transfering data from MS-SQL to My-SQL Server

    Error description:

    1)[ADO NET Destination [231]] Error: An exception has occurred during data insertion, the message returned from the provider is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"BookID") VALUES (p1)' at line 1

    2)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ADO NET Destination" (231) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (234). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    Please help me.

    Thank You

    Vaibhav

  • Please see the follow-up post I made about writing data to MySQL.

    http://blogs.msdn.com/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx

    You should be able to find the work around for your issue there.

  • Hi Matt,

    Thank you very much.

    I am able to connect My-SQL and successfully transfer my data from SQL to My-SQL

  • A comment on my post about writing to a MySQL database inspired me to put together this post about how

  • Hi Matt,

    I am trying to transfer data from MySQL to SQL.

    I got as far as being able to see the "Available External Columns".

    After selecting (drag&drop) the SQL Server Destination, I don't see any of those "Available External Columns" from the ADO.Net source.  I guess I don't know how to select the input columns.  Please help.  Thanks.

    I said "transfer" earlier but it's more of a convert than transfer.  I want to learn more about SSIS & MySQL2SQL.

    Bernie

  • Couldn't get it working with SSIS, but got it working using a powershell script.  You need to install the .Net Connector for MySQL, plus the powershell runtime if you haven't got it already installed. Need to be careful of how MySQL handles date variables (need to be ANSI format - powershell insists on formatting it to the country format of the host OS rather than leave untouched) and \ characters need to be doubled up in the source data otherwise they get stripped out.

    Change connection strings as needed, use standard ADO.Net syntax.

    I'm sure there is a better way of doing it than this, but it works well enough for my needs.

    function CopyDataFromMSSqlToMySql {

       ##$file = "C:\\Program Files\\MySQL\\MySQL Connector Net 6.2.3\\Assemblies\\MySql.Data.dll\"

       [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

       # Establish connection

       $mssql_connectionString = "Server=(local);Integrated Security=SSPI;Database=Adventureworks"

       $mysql_connectionString = "server=localhost;uid=xxxxx;pwd=yyyyy;database=test;"

       # Create MS SqlConnection object and connect

       $mssql_con = New-Object System.Data.SqlClient.SqlConnection

       $mssql_con.ConnectionString = $mssql_connectionString

       $mssql_con.Open()

       # Create SqlCommand object, define command text, and set the connection

       $mssql_cmd = New-Object System.Data.SqlClient.SqlCommand

       $mssql_cmd.CommandText = "select EmployeeID, replace (LoginID, '\', '\\') as loginid, cast(CAST(YEAR(birthdate) AS VARCHAR(4)) + '-' +

              CAST(MONTH(birthdate) AS VARCHAR(2)) + '-' + CAST(DAY(birthdate) AS VARCHAR(2)) as varchar (10)) BirthDate from HumanResources.employee;"

       $mssql_cmd.Connection = $mssql_con

       # Create SqlDataReader

       $dr = $mssql_cmd.ExecuteReader()

       If ($dr.HasRows)

       {

           # connect to MySQL

           $mysql_connection = New-Object MySql.Data.MySqlClient.MySqlConnection

           $mysql_connection.ConnectionString = $mysql_connectionString

           $mysql_connection.Open()

           $mysql_cmd = New-Object MySql.Data.MySqlClient.MySqlCommand

           ## Write-Host "Number of fields: " $dr.FieldCount

           While ($dr.Read())

           {

              $employeeid = $dr["employeeid"]+290

              $loginid = $dr["loginid"]

              $birthdate = $dr["birthdate"]

              ##Write-Host $birthdate

              ##$mysql_command = "insert into employees (employeeid, loginid, birthdate) values ('$employeeid', '$loginid', '$birthdate' )"

              $mysql_command = "replace into employees (employeeid, loginid, birthdate) values ('$employeeid', '$loginid', '$birthdate')"

              # Write-Host $mysql_command # don't run it yet

              $mysql_cmd.connection = $mysql_connection  

              $mysql_cmd.CommandText = $mysql_command

              $mysql_da = New-Object MySql.Data.MySqlClient.MySqlDataAdapter ($mysql_cmd)

              $dataSet = New-Object System.Data.DataSet

              $mysql_da.Fill($dataSet, "test1")

           }

           # Close MySQL connection

           $mysql_connection.Close();

       }

       Else

       {

           Write-Host "No matching source data"

       }

       Write-Host

       # Close the data reader and the connection

       $dr.Close()

       $mssql_con.Close()

    }

  • Thank you for this article.

    Works very well to read some data from a mySQL DB and write it into a MSSQL DB

  • Hi,

    How do you mange to retain the password in Connection Manager? Although I've ticked the save password, each time i close it, it just disappear.

    Thanks.

  • It should save the password if your ProtectionLevel setting on the package is anything except "DontSaveSensitive".

    Certain providers will lose their password when you open the connection manager UI, but will save the value in the package (encrypted) to use at runtime.

    Another alternative is to set the connection string using a Configuration (file, sql, env var, etc).

  • No matter what protection level we choose the SSIS pkg will not save the connection string password. I can save it once and test the connection it it returns success but trying to run the pkg results in "Access denied for user 'root' (using password: NO) ".  Any help would be appreciated but not expected as I've seen over 100 posts relating to this same issue and every response says that either the pkg is corrupt or the security level isn't set properly. I've spent days on this issue.  Configuration files do not solve the problem either.

  • I believe with this provider, the password is lost each time you open the UI. But if you set the value once and save the package, you should be ok.

    On the Connection Manager UI, click the "All" tab to see all of the properties. Try setting "Persist Security Info" to True. I noticed I have it like that in the screenshots in this post.

Page 1 of 2 (19 items) 12