Have Visual Studio 2008 R2 with SP 2 installed.  Due to a merger we now have a MySQL database that we need to update from SSIS.  Everything works except for the table insert or update.  Would upgrading to SP 3 or SP 4 maybe help with that?  We have installed the latest driver from MySQL.  Have tried the ADO.Net and ODBC drivers with similar results when we try to update the database.

• Moved by Jack-ZhaiMicrosoft contingent staff Wednesday, June 24, 2015 7:34 AM Move to a more appropriate forum.

Hi mkbea,

Since this issue is related to the SSIS, I help you move this case to the SQL Server Integration Services forum for dedicated support.

Sincerely,

Jack

Hi,

you can update table, using OleDB command (with OleDb connection) in a dataflow task or  simple use SQL task with any connection.

What are your results in updating?

Hi,

I am getting the two following errors when I attempt to do the row insertions:

"[ADO NET Destination [189]] 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
'"risk_type_code", "short_description", "long_description") VALUES (p1, p2, p3); ' at line 1

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ADO NET Destination" (189) failed with
error code 0xC020844B while processing input "ADO NET Destination Input" (192). 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."

I tried several of the suggestions I found concerning SSIS updating MySQL database, but none have worked for me.

Thanks in advance for any workable solution(s).

Check the commercial COZYROC ODBC Destination component. It has been tested and it works with MySQL.

Hi again, as i understand, your SQL statment is wrong.

try to change

VALUES (p1, p2, p3);

to

VALUES ('p1', 'p2', 'p3');

It should work.

If you need to use parameters, so you need to write:

VALUES (:p1, :p2, :p3);

And describe this parameters in "Parameters description" in SQL task

Hi mkbea,

Please note that the VALUES portion has parameter names, and not the actual values in the error message. 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.

To work around this issue, please use ODBC connector. For more details, please refer to the blog below:
http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx?wa=wsignin1.0

Thanks,
Katherine Xiong

A team effort here solved the issue.  Replaced the "ADO NET Destination" with the "Script Component".  This allow us to imbed the "Insert" SQL into a generated C# program inside the Script.  Thanks to all who responded with suggestions.

• Marked as answer by mkbea 12 hours 24 minutes ago