Welcome to MSDN Blogs Sign in | Join | Help

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.

Published Wednesday, January 07, 2009 5:32 PM by mmasson
Filed under:

Comments

# SSIS Team Blog : Connecting to MySQL from SSIS

PingBack from http://blogs.msdn.com/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

Wednesday, February 25, 2009 12:53 PM by SSIS Team Blog : Connecting to MySQL from SSIS

# re: Writing to a MySQL database from SSIS

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.

Monday, March 02, 2009 3:05 AM by romorelli

# re: Writing to a MySQL database from SSIS

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

Monday, March 02, 2009 1:58 PM by mmasson

# re: Writing to a MySQL database from SSIS

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.

Wednesday, March 11, 2009 8:53 AM by romorelli
Anonymous comments are disabled
 
Page view tracker