SSIS Execute SQL Task Error with MySQL ODBC Connection

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
indyx
Posts: 1
Joined: Fri 04 Aug 2017 12:15

SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by indyx » Fri 04 Aug 2017 12:40

I create "Execute SQL Task" with properties:
ResultSet: None
ConnectionType ODBC
Connection via devart odbc driver for mysql
SQLSourceType: Direct input
SQLStatement: delete from tempt

Connection work properly for ODBC Source and ODBC Destination component.
Table tempt realy exists. Query work in MySQLWorkbench.
Task have no parameter mapping and no result binding.

Package execute with error:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "delete from tempt" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is it?...

ViktorV
Devart Team
Posts: 2354
Joined: Wed 30 Jul 2014 07:16

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by ViktorV » Mon 07 Aug 2017 11:47

We investigated the issue according to the description. Unfortunately, we could not reproduce the error in our test environment. In our project we created "Execute SQL Task" with the parameters specified by you and successfully deleted data of several test tables. For further investigation please compose and send us the simplest SSIS project (or SSIS package *.dtsx) via form e-support: https://www.devart.com/company/contactform.html

kamil.frankiewicz
Posts: 1
Joined: Tue 31 Jul 2018 08:24

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by kamil.frankiewicz » Tue 31 Jul 2018 08:59

same problem here.

steps to reproduce:

1) In SSIS create Execute SQL Task with ODBC connection using devart driver.

2) configure this Task with:

SQLSourceType: Direct input
ResultSet: none

SQLStatement:

UPDATE main_table a
INNER JOIN staging_table b ON a.varchar_column = b.varchar_column
SET a.col1 = b.col1, a.col2 = b.col2, (....)
WHERE a.hash_column = b.hash_column

This task WORKS when there are data that satisfy [a.varchar_column = b.varchar_column] condition.

The problem occurs ONLY WHEN THERE IS NOTHING TO UPDATE.
1) When staging_table is empty
2) or has rows that don't match [a.varchar_column = b.varchar_column]

you get:
[Execute SQL Task] Error: Executing the query "UPDATE main_table a INNER JOIN staging_table b ON a.varcha..." failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

ViktorV
Devart Team
Posts: 2354
Joined: Wed 30 Jul 2014 07:16

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by ViktorV » Thu 02 Aug 2018 14:28

Thank you for the information. We investigated the issue and found out the cause of the described behavior. In the case you are describing, SSIS uses the SQLExecDirect ODBC function to execute the query. According to Microsoft documentation https://docs.microsoft.com/en-us/sql/od ... erver-2017 if the query has not affected any records will return the result SQL_NO_DATA (for the ODBC 3.x specification). Our driver and SSIS use the ODBC 3.x specification, however, in the described case, SSIS implements the behavior as ODBC 2.x . That's why, when the result of SQL_NO_DATA is received, the error "Error HRESULT E_FAIL has been returned from a call to a COM component" is returned.
In the next release of ODBC Driver for MySQL, we will add the possibility to force ODBC 2.x behavior.
You can use the night build of ODBC Driver for MySQL with this functionality at https://devart.com/pub/dac/DevartODBCMySQL.exe
To solve the issue, please try to enable the "Force the ODBC 2.x behavior" checkbox on the Advanced settings tab in the configuration dialogbox of ODBC Driver for MySQL.

Post Reply