Page 1 of 1
SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Fri 04 Aug 2017 12:40
by indyx
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?...
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Mon 07 Aug 2017 11:47
by ViktorV
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
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Tue 31 Jul 2018 08:59
by kamil.frankiewicz
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.
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Thu 02 Aug 2018 14:28
by ViktorV
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.
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Thu 21 Feb 2019 08:23
by RayleeMarshall
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Thu 21 Feb 2019 10:43
by mrobak
ViktorV wrote: ↑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.
Thank you, I have a similar problem!
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Fri 22 Feb 2019 12:13
by ViktorV
Thank you for the interest to our product.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Fri 22 Nov 2019 09:34
by counter
RayleeMarshall wrote: ↑Thu 21 Feb 2019 08:23
These errors are version specific. My experience is that version 6.9.X of MySQL .NET Driver is working in my environment (SQL2016 & MariaDB 10.1 I tried 6.10.X & 8.0.X and both return the "Failed to create an IDataAdapter object".
Yes, I also think so, and have the same issue
Re: SSIS Execute SQL Task Error with MySQL ODBC Connection
Posted: Mon 03 Jan 2022 13:38
by herberthaul
ViktorV wrote: ↑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.
I will check it lets see will it work or not