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.

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.

ODBCConnector/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.

Comments (21)

  1. edub71 says:

    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?

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

  3. vaiwar says:

    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

  4. mmasson says:

    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

  5. vaiwar says:

    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

  6. mmasson says:

    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.

  7. vaiwar says:

    Hi Matt,

    Thank you very much.

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

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

  9. berniesu says:

    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

  10. John says:

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

    }

  11. Dani says:

    Thank you for this article.

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

  12. leons says:

    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.

  13. mmasson says:

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

  14. Not saving password: Access denied for user 'root' (using password: NO) says:

    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.

  15. mmasson says:

    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.

  16. SqlMind says:

    @Not saving password: Access denied for user 'root' (using password: NO). In the connection manager property try using the connection sting as an expression. your expression cal look something like this "Driver={MySQL ODBC 5.1 Driver};server=000.00.000.00;uid=user_name;Pwd=password;database=database".

  17. SqlMind says:

    I am running a SSIS package that calls an SP form Mysql DB via a SP.

    1. Get a list of values in a sql server table.

    2. pass each value as a parameter to an SP in mysql.

    3. Once the SP returns data, insert it into a table in sql server after suitable data conversions.

    When the SP returns data, the package runs fine. However when the SP returns 0 rows then the package fails with the error:

       [ADO NET Source 1 1 [126]] Error: System.NullReferenceException: Object reference not set to an instance of an object.

          at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

          at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper).

    My expectation is the package should succeed, not insert any rows into the table if the SP returns 0 rows.

    I mean why is it throwing an exception in ADO NET Source…

    Note, the package succeeds if the SP returns more than 0 rows.

    Workarounds are also welcome.

  18. Atif says:

    I have created an ADO.Net destination to use a ".Net ProvidersMySQL Data Provider" connection that has been provided with all required info.

    When running this package I am getting this error

    "[ADO NET Destination [2]] 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 '"order_no", "order_ref_no", "customer_code", "date_order_created", "qty", "produ' at line 1"

    Any thoughts?

  19. David says:

    We are able to create the package perfectly in Visual Studio

    But when we try to run the package from SSIS, we get the following error:  Error: 2014-04-29 17:38:29.09     Code: 0xC001000E     Source: GScore_Pull_Daily      Description: The connection "{D1B9D248-1689-47FE-A91B-AA407E255C6A}" is not found. This error is thrown by Connections collection when the specific connection element is not found.  End Error  

    We are currently running the 32 bit driver on a 64 bit server which is running the 64 bit version of SQL 2012.

    Any help would be appreciated.  

    Thank you,

    David

  20. gurvi says:

    Hi Matt,

    i am unable to connect using the ADO.NET , getting the error to use POST 4.1 passswords.

    earlier i was able to change the session in My sql to use the old style passwords.

    Now the My SQL server has been upgraded and it doesnt allow the old style passwords.

    Thanks

    gurvi

  21. Islam says:

    Thanks this was perfect and exactly what I needed.