Trouble connecting MYSQL in SSIS

  • I connect to MYSQL in SSIS with MYSQL data provider. I am getting the errors below. I also tried via ODBC using this code Driver={ODBC Driver 13 for SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

    I just cant seem to figure out where the problem is. Please assist?

     

    [ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"uid", "product_uid", "app_menu_filter_uid") VALUES (p1, p2, p3); INSERT INTO...' at line 1

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (2) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (9). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

  • I could connect ODBC using this string"Driver={Mysql ODBC 8.0 ANSI Driver};Server=Localhost;Database=Marketing;UID=Marketing;PWD=Musica123;" now i am getting this error:

    TITLE: Microsoft Visual Studio

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

    Pipeline component has returned HRESULT error code 0xC0208457 from a method call.

    Error at Data Flow Task [ADO NET Destination [194]]: Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are:

    ERROR [42000] [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.4.21-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"app_menu_filters9"' at line 1

    Could not load provider specific information because the specified directory "C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ProviderDescriptors\" does not exist.

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

    BUTTONS:

    OK

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

  • I'm no expert on MySQL at all (never used it), but I'm a little confused about the reference to MariaDB in the error message. I know MariaDB started out from the same source code as MySQL when many of the original developers left the MySQL project, once the MySQL product was sold to Oracle - and as such the two databases are almost identical, even now.

    But if it is indeed MariaDB you're using, maybe it would be better to use the MariaDB ODBC driver?https://mariadb.com/kb/en/about-mariadb-connector-odbc/#creating-a-data-source-with-mariadb-connectorodbc

    But maybe more importantly, the error message seems to indicate that you use a .NET Destination component. I would assume that you should use a ODBC Destination component instead?

    https://docs.microsoft.com/en-us/sql/integration-services/data-flow/odbc-destination?view=sql-server-ver15

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply