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

Submitted by il_masacratore on 10 September, 2010 - 11:51

 

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

 

The first aim will do so through an ADO.NET Destination 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 appears an error like the following check everything with the preview or try and do 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 we can retest and get 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 how they built the insertions of 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 joined the issue of modifying the destination sql_mode MySQL allow us to load properly.

 

 

 


 

* Please 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). Más info aquí. More info here.

 

 

 
Google
 
     

Similar entries

  • MySql: Slow queries, queries to optimize control

    In MySQL there is a parameter that enables logging of queries that take more than x seconds to execute calls Slow Queries. Basically what it does is put them in a log file and then check with mysqldumpslow. This mechanism may be useful to improve the overall performance of the database if we reduce the value of a progressive manner to the execution times of "leave" before declaring a query such as "slow" or slow query...

     

  • Mysql: Daily Checklist

    Th

    en leave a small post on how to mount an automatic notification via mail that tells us what is the status of our database. For example we can manage it a bit before starting our work day and before we leave or while on vacation (jaja. ..). This example is about a MySQL 5.x running on Debian. Steps as follows...

     

     

     

  • How to connect to a remote MySQL database

     

    MySQL has some special features when making a connection from a remote client that if we do not know can complicate access to a MySQL database from a different machine that hosts the database.

    With other databases such as Oracle or SQL Server, once that no firewall or anything like that prevents us from the client machine access to the server, using normally data acces from a database user we can 'enter'.

    With MySQL, although access to the port, usually 3306, is open, the database can be configured to shut out external connections, and the result is the same as if the port was closed by a firewall:

     

    telnet mysql.dataprix.es 3306
Trying 188.166.233.199...
telnet: connect to address 188.166.233.199: Connection refused
telnet: Unable to connect to remote host

     

    If you get this result should consult the file /etc/my.cnf, and checking for bind-address variable or skip-networking.

    If skip-networking is and is not discussed, edit the file and delete it, or make a comment to have no effect and allow external connections:

     

  • GRANTS WITH GRANT OPTION: The transitive property granting privileges

     

    Grant command is used to grant to user's databases generic privileges or permissions over objects.

    It's syntax is: 
    GRANT [system_privileges | roles] 
    TO [user | role | PUBLIC] {WITH GRANT OPTION} 
    to grant generic privileges

  • The error ORA-30926 as a result of a Merge

    ORA-30926 error usually occurs when performing merge operations, and typically we leave something descolocados as the description of it does not give too much information about what is happening: 
    ORA-30926: unable to get a stable set of rows in the source tables.

    Normally this error occurs when the merge operation to target a row to be updated on it more than one row in the table source. As the engine does not know what to choose register returns an error. It is a problem of duplication in the origin table.

    Example:

    • We have: 
      TABLA_ORIGEN with the values 
      ID Description 
      1 'The first value' 
      1 'The value with duplicate id' 
      2 'Another value'