Description:
Hi guys,
Can not use ADO NET MySQL connector in SSIS for inserting data. All the time the error appear:Unknown column 'p1' in 'field list'
I've found very similar bug has already been reported: https://bugs.mysql.com/bug.php?id=72468
The can be reproduced uisng SSIS and latest version of MySQL (mysql-connector-net-8.0.13): for ADO NET. The ANSI mode for the MySQL connection has beend set. Simply create a SSIS package copying data from SQL server to MySQL table using ADO NET Destination for MySQL.
MySQL version is 10.1.37-MariaDB-0+deb9u1 Debian 9.6
Working with Visuao Studio 2013, and IntegrationServices 12.0
An exception has occurred during data insertion, the message returned from the provider is: Unknown column 'p1' in 'field list'
When I have disabled setting ANSI for the connection to MySQL, ther an error is different and pointing to SyntaxIssue. But it allow to see SQL command send to MySQL and it not seems to be correct I think:
INSERT INTO test_IG_SSIS ("rec_id") VALUES (p1).
wuld apprecuate you feedback other then "can not repro/Can't repeat" - as this one seems to be well known old issue for users that you have resolved as "Can not repeat".
thank you,
Ivan
How to repeat:
create a SSIS package copying data from SQL Server table to MySQL table, using ADO Net destination with latest version of the driver. Do necessary mapping.
Set session sql_mode property to ANSI for MySQL connection used by SSIS. Run the package.
Data can not be inserted to MySQL and the error appears.
Suggested fix:
data shoulds gous to MySQL database from SSIS using ADO Net MySQL connector
