none
Error in ADO.Net Destination when using MySql provider

    Question

  • I am using RC0.  After I typed the table name into the destination component (No table names appeared in the drop down) I was able to map a field (id) to the MySql destination and then run the package, but I got this error:
    >>
    Error: 0xC020844B at Data Flow Task, ADO NET Destination [168]: 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 '"id") VALUES (p1), (p1), (p1), (p1)' at line 1
    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ADO NET Destination" (168) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (171). 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 am only trying to insert to one column, but it looks like the component is incorrectly generating 4 parameter placeholders
    (p1), (p1), (p1), (p1) instead of one?

    Thanks,
    Al

    Update:  08/20/08  I installed the latest MySQL Connector.Net version 5.2.3.  Now the drop down on the destination component shows the table names in the drop down whereas the previous version did not.  I still get a runtime error, but this time it is:
    >> [ADO NET Destination [52]] 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 '"client_data_1", "business_name", "client_data_2", "client_data_3", "client_data' at line 1<<

    How do I change the syntax on the destination?  It looks like it's using double-quoted names. I think MySQL wants backticks?  Any advice?  I'm surprised I haven't gotten any responses yet.  Am I the only one getting this error?  Is it fixed in the 2008 RTM version?  Thanks.
    Monday, July 28, 2008 9:25 PM

All replies

  • Having the same problem. Would be great if somebody could help!

     

    Thanks!

    VM

    Wednesday, August 27, 2008 4:49 PM
  • VM,

    Oh, I'm so glad I'm not alone. :-)

    Somebody says they got it working here:

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

    I wish I knew their secret.  I have since upgraded to RTM version from RC0 and still no luck.  I could really use it for my current project.  I'm hoping/praying someone comes to our aid soon.

    I've submitted this as a bug:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=364381

    Cheers,
    Al
    Thursday, August 28, 2008 5:04 PM
  • I'm one step behind you I think... How do you manage to get the mysql datasource in the drop down list in order to build the model from the database?
    Sunday, August 31, 2008 8:51 PM
  • Seems like working for me with devart.com mysql ado driver.
    Monday, September 01, 2008 3:20 AM
  • I had the same problem,

    I am welling to get my data source for the OLAP DB (analysis services) as a MYSQL DB and when I used the connecter 5.2.3 I can view the tables but after tables' selection in the data view it returns the same error.

    Anybody found a solution for that?

    Monday, October 13, 2008 7:29 AM
  • How is sql_mode set for your mysql?
    SELECT @@global.sql_mode;

    SELECT @@session.sql_mode;

    Does it include 'ANSI_QUOTES'?
    That is how I could get the mysqldirect.net from devart to work for me in the ADO.Net Destination.

    Thanks,
    Al


    Friday, October 17, 2008 1:38 PM
  • Anybody found a solution for thist problem?

    I am having the same problem writing back to MySQL. Tried many different versions of ADO .Net connector... but with no avail.

    Help would be appricated.

    Benjamin Leroux
    Thursday, September 03, 2009 7:33 PM
  • Guys,

    Your calls have been finally answered. Check CozyRoc ODBC Destination component. It includes support for both regular and bulk-load of data. It has been already tested and supports mySQL. Cheers!
    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    Wednesday, September 09, 2009 10:58 PM
  • For SSIS 2008 this workaround got the ADO NET Destination inserting rows into MySQL for me:

    http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx?wa=wsignin1.0

    Note that for more than a trivial number of rows it may not be viable for everyone as it inserts one row of data at a time so is quite slow. My tests only yielded a throughput of about 240 rows/minute. For larger sets consider exporting data to a flat file and importing it into MySQL using LOAD DATA INFILE.

    If using SSIS 2012 using the ODBC Destination will provide better performance than the ADO NET Destination, but still not great. In my testing I was seeing 800 rows/minute throughput to MySQL with a batch size of 1,000 and about the same with a batch size of 10,000.


    Make everything as simple as possible, but not simpler.
    -Albert Einstein

    Monday, August 27, 2012 2:49 PM