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 : 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?
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 ] 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.
Oh, I'm so glad I'm not alone. :-)
Somebody says they got it working here:
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:
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?
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/
For SSIS 2008 this workaround got the ADO NET Destination inserting rows into MySQL for me:
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.