SSIS: Workaround to two errors for no apparent reason when we insert data in MySql

For whatever reason we may have to develop a suite of Microsoft Integration Services that we move data from any source to a table in a MySQL database.

The first aim will be to do with a Destination ADO.NET and MySQL provider for the connection. If we do well to insert the data directly, to create the target, select the connection and then select the table an error like the following check everything with the preview or when I make the assignments.

This error is due to the compatibility mode of ansi sql mysql database where we try to load the data.To solve this we must connect to MySQL server and change the compatibility of ansi sql database *:

TOCA: ~ # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 77

Server version: 5.0.51a-24 + lenny3-log (Debian)

Type 'help,' or '\ h' for help. Type '\ c' to clear the buffer.

mysql> select @ @ global.sql_mode;

+-------------------+

| @ @ Global.sql_mode |

+------------------- +

| |

+-------------------+

1 row in set (0.00 sec)

mysql> set global sql_mode = 'ANSI';

Query OK, 0 rows affected (0.00 sec)

mysql> select @ @ global.sql_mode;

+------------------------------------------------- ------------+

| @ @ Global.sql_mode |

+------------------------------------------------- ------------+

| REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI |

+------------------------------------------------- ------------+

1 row in set (0.00 sec)

mysql> exit

If re-test and we have a preview of the table or edit the mappings between columns, but we find another error when running the package:

[ADO NET Destination [843]] Error: Exception when inserting data.The provider returned message is: Unknown column 'p1' in 'field list' "

The driver has a problem and not let us work well with parameters (which is constructed as inserts records in the destination) so we have to do another workaround to solve this problem working with ADO.NET Destination on arrival but with a ODBC source in the connection. This coupled with the issue of modifying the destination sql_mode MySQL allow us to load correctly.


* Note that the support we can change globally and in the instance or session level only (by which we should add a command execution in the first instance to modify the value of @ @ SESSION.sql_mode).More info here.