Writing to a MySQL database from SSIS


A couple of users reported being unable to use the ADO.NET destination to insert data into their mysql databases. When I originally tried this out, it worked, but it seems like changes made since the early 2008 CTPs have made us incompatible with MySQL. We do have a bug logged to make this more flexible, but the good news is that in the meantime there is a workaround when using the ODBC connector.

For the ADO.NET Destination to work properly, the MySQL database needs to have the ANSI_QUOTES SQL_MODE option enabled. This option can be enabled globally, or for a particular session. To enable it for a single session:

  1. Create an ADO.NET Connection Manager which uses the ODBC driver
  2. Set the connection manager’s RetainSameConnection property to True
  3. Add an Execute SQL Task before your data flow to set the SQL_MODE – Ex. set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'
  4. Make sure that your Execute SQL Task and your ADO.NET Destination are using the same connection manager.

Setting the RetainSameConnection property to True will ensure that your Execute SQL Task and ADO.NET Destination are in the same session.

Note, I recommend using the ODBC Driver when writing to the MySQL database, because the MySQL .NET Connector has an additional blocking issue. If you try it out, you’ll get an error which looks something like this:

Error: 2009-01-05 12:03:47.79
   Code: 0xC020844B
   Source: Data Flow Task 1 Destination - Query [28]
   Description: 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 nea
r '"name", "date", "type", "remark") VALUES (p1, p2, p3, p4), (p1,p2,p3,p4), (p1,p2' at line 1
End Error

Note that the “VALUES” portion has parameter names, and not the actual values. This appears to be an issue with the value the MySQL provider returns for its ParameterMarkerFormat. I did find a bug that was opened against them, but it looks like they decided not to fix it. I’ve heard that the DevArt dotConnect drivers do not have this problem, but I haven’t been able to try them out myself.

Comments (17)

  1. romorelli says:

    Hi,

    I have a problem about this theme.

    I need to connect a database SQL and a database MySQL to do INSERT, MODIFY and DELETE using a SSIS Package.

    I did this connection without problems.

    The problem is: with the SQL 2008 we have the tool "ADO NET DESTINATION" to do the tasks easily. But with this tool I can do

    only the INSERT into MySQL. And to do the MODIFY and DELETE?

    Could you help me or give me some idea?

    With others tools I can only use "OLE connections".

    I tried many things and tools, but without success.

    Thanks.

    romorelli.

  2. Hi Romorelli,

    You’ve inspired me! I turned the reply into a new post:

    http://blogs.msdn.com/mattm/archive/2009/03/02/how-do-i-do-update-and-delete-if-i-don-t-have-an-oledb-provider.aspx

    I hope one of those solutions works out for you.

    ~Matt

  3. romorelli says:

    Hi Matt,

    Thank you very much!

    I used the "Script Component" and now my SSIS Package is working with INSERT, MODIFY and DELETE between SQL Server and MySql… 😉

    romorelli.

  4. sdoubt@fulcrum.ca says:

    Thanks very much for this post, Matt.

    Currently I’ve got a couple of SSIS projects in production that use the MySQL 5.1 ODBC connector. Unfortunately, they perform very slowly, because of the SQL -> MySQL transfer.

    Is there any news on when the issue of connecting to the MySQL .NET connector might be resolved? (The one you’ve mentioned in your post above.)

    Thanks in advance for any news you might have.

    -Simon

  5. Hi Simon,

    Unfortunately, the issue with the .NET Connector I mentioned above (param substitution) is in code owned by MySQL. We don’t have any control over fixing that one.

    The SSIS issue with forcing ANSI quotes (which can be worked around, as described above) has not been resolved yet. We haven’t received any requests through customer support to fix the issue in a cumulative update, so it’s unlikely it will be fixed before the next major release.

  6. sdoubt@fulcrum.ca says:

    Matt,

    Thanks very much for the reply.

    I might try the 6.1 version of the MySQL .Net connector to see if it’s been resolved. I’ll post here with any news.

  7. sosgood says:

    We are transferring data from SQL Server to MySQL using SSIS and while it works, the performance is unacceptable. It is creating an insert statement for every record rather than using a bulk insert. While the OLE destination has the fast load option the ADO.net doesn’t. Is there another way to connect to MySQL without using the ADO.net destination? Any third party apps? I can’t believe people are content with the performance. We move 100’s of thousands of records and it’s inserting less than 1000 records a minute. Any help would be appreciated. MySQL 4.0.45, ODBC 5.1, MySQL .net connector 6.1.2

  8. Yes, unfortunately ADO.NET doesn’t provide a generic Bulk Load interface. We’ve added bulk support for SQL Server (using the SqlBulkCopy interface), but don’t have a good way to do it for third party components.

    I haven’t tried it out myself, but CozyRoc has an ODBC Destination that supports Bulk Load.

    http://cozyroc.com/ssis/odbc-destination

    You might want to check with them and see if it’s compatible with the MySQL ODBC provider.

  9. Steff Sullivan says:

    Hi, I've followed the above advice, but when it comes to the insert into a MySQL table the 'set sql_mode' fix got rid of the value error and I'm now getting failures on:

    An exception has occurred during data insertion, the message returned from the provider is: There is already an open DataReader associated with this Connection which must be closed first.

    I found a post where somebody was having a similar issue, but this was with a SQL server box, and relates to MultipleActiveResultSets.

    Does any one have any ideas on how best to solve this issue?

  10. Hi,

    I am doing all the stuff you mentioned in your post.

    All works fine except when a table contains a numeric field with a null in first row, then the process acts as if the null was char type and when there is a numeric value in the next rows, the followinfg error occurs :

    Erreur : 0xC020844B à Copie tbl_personne, ADO NET Destination [1093]: Une exception s'est produite lors de l'insertion des données ; le message retourné par le fournisseur est : Impossible d'effectuer un cast d'un objet de type 'System.Decimal' en type 'System.Char[]'.

    Did you meet this kind of error ? Do you have a solution ?

    If the value in first row of the numeric field is numeric (ie 6), then no problem, null and numerics are well writen.

    This wordked fine with DTS in SQLServer 2000.

    Thanks in advance for your help

  11. Mike8Mike says:

    Is this still the best solution to replicate and keep a database table on MSSQL updated on MySQL?  I am REALLY getting my butt kicked trying to do this…I am through the drivers issue now I continue the battle:

    social.msdn.microsoft.com/…/2400d746-001b-405c-b4f3-a5bd98a9d681

    Any help would be most appreciated.

  12. Robin Tanner says:

    I've tried what you have proposed and I cannot pull data from an odbc driver using an ado.net source without getting this error when I try to map the columns. This is a subset this line repeats about 300 times at least. What am I doing wrong? I can find nothing on the web about this error.

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

    ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.13-enterprise-commercial-advanced]

  13. sonali says:

    A Big Thanks for this post !! I am a beginner in SSIS and YOU saved me the very first Day !!

  14. Shahmir Khan Jadoon says:

    I am currently working on SSIS but my BOSS now asked me that we are trying to shift from MS SQL to MYSQL,

    So is there any free Integration services tool in MYSQL ?

    I will be grateful for your help.

  15. Carlos Castaneda says:

    You've allready solve my problem!

    Thanks

  16. Euler Vicente says:

    Your post solved my issue. Thank you!

Skip to main content