Bug #93874 Can not use ADO NET MySQL connector in SSIS for inserting data
Submitted: 10 Jan 2019 12:08 Modified: 15 Jan 2019 7:58
Reporter: Ivan Gorbliuk Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (Server 2012R2)
Assigned to: CPU Architecture:Any (x64)
Tags: ADO, connectoror, ssis

[10 Jan 2019 12:08] Ivan Gorbliuk
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
[15 Jan 2019 7:58] Ivan Gorbliuk
It seems I've used not exactly MySQL server. Need to verify it on MySQL. Changed status to Unsupported