SSIS - ODBC Destination Error with MySQL

Drew Baumgartner 1 Reputation point
2022-07-27T12:37:47.357+00:00

I'm getting the following error when trying to configure my ODBC Destination for a MySQL table.

Exception from HRESULT: 0xC0014020
Error at DFT - Insert into MySQL [ODBC Destination [476]]: SQLSTATE: 42S22, Message: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.38-0ubuntu0.16.04.1+esm1]Unknown column 'Syn?e' in 'field list';

I don't have a column named 'Syn?e' in the table. I've found some articles saying that triggers may cause this issue but I don't have any triggers on the table. I've dropped and created the table fresh and still get this error. I can't find any other information regarding this error :(.

I am successfully able to use an ADO.NET Destination to write data using the same MySQL table but I would like to get the ODBC Destination to work so I can compare speeds (the ADO.NET writes only 17-18 inserts per second...so it takes ~15 minutes to load the full table of ~14,700 rows).

225234-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,503 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hamici Mohamed 10 Reputation points
    2023-05-30T07:30:03.62+00:00

    Solution

    The following property needs to be set in the ODBC DSN created for MySQL on the secure agent installed machine.SET sql_mode='ANSI_QUOTES'

    renditionDownload

    2 people found this answer helpful.
  2. ZoeHui-MSFT 34,836 Reputation points
    2022-07-28T01:44:20.397+00:00

    Hi @Drew Baumgartner ，

    Will it be related with your MySQL ODBC Driver？

    Here is a same thread that he solved the issue by changing MySQL ODBC 8.0 ANSI Driver for ODBC Destination to MySQL ODBC 8.0 Unicode Driver.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. Drew Baumgartner 1 Reputation point
    2022-07-28T13:12:46.253+00:00

    I did try the MySQL ODBC 8.0 Unicode Driver and it fails with a different error message.

    For some reason I'm having issues uploading an image so if it doesn't show up the plain text of the error is listed below.

    TITLE: Microsoft Visual Studio

    ------------------------------

    There was an error displaying the preview.

    ------------------------------

    ADDITIONAL INFORMATION:

    ERROR [42000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.38-0ubuntu0.16.04.1+esm1]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 '"SyndigoTarget"' at line 1 (myodbc8w.dll)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Researching the above error I found some suggestions to change the sql_mode but I'm already changing the sql_mode for the session via an Execute SQL Task as the first step in the SSIS package.

    For reference here are the sql_mode settings for the MySQL server (which I am not able to change).

    'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

    Below is the Execute SQL Task in the Control Flow that changes the sql_mode for the session before the Data Flow Task.

    SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';

    I found another suggestion (https://stackoverflow.com/questions/68514270/mysql-odbc-excel-datasource-error-you-have-an-error-in-your-sql-syntax) for trying different versions of the ODBC connector. I used the latest version (8.00.30) and two older versions (8.00.25) and (5.3.13) and they all produce the same error message for both the ANSI and Unicode drivers. I don't have a good understanding of what the error is but from what I can find it seems like the connector might be wrapping the SQL with invalid syntax? I'm not sure if this is the issue or how to fix it.225853-mysql-unicode-connector-error.png

  4. Drew Baumgartner 1 Reputation point
    2022-07-29T13:28:41.857+00:00

    I have Run64BitRuntime set to False but I've tried setting it to True and both produce the same error messages.

    I do have the 32-bit drivers configured. I have enabled logging queries for the ODBC connector and below is the query that generates the error message.

    -- Query logging
    --
    -- Driver name: MySQL ODBC 8.0 Driver Version: 08.00.0030
    -- Timestamp: 220729 8:58:18

    1659099499:select * from "SyndigoTarget";
    1659099499:Using direct execution;
    1659099499:query has been executed;
    1659099499: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 '"SyndigoTarget"' at line 1;

    It appears that the table name, SyndigoTarget, is being wrapped in double quotes. Since I don't have ANSI_QUOTES enabled on the MySQL server I'm sure this is where the error is coming from. I have an Execute SQL Task that is enabling ANSI_QUOTES for the session but I'm guessing the session is only active while the package is being executed. I'm assuming that while configuring a Data Flow Task the queries that are sent to the MySQL server use a different session that uses the default sql_mode which doesn't have ANSI_QUOTES enabled.

    I guess I'm stuck not being able to use the ODBC connector unless I can get permission to change the sql_mode on the MySQL server.

    0 comments