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: 3168
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: 3168
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.

RayleeMarshall
Posts: 1
Joined: Tue 19 Feb 2019 11:06

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by RayleeMarshall » Thu 21 Feb 2019 08:23

These errors are version science bio review dianabol for sale weight loss pills that work fast where to buy sarms sarms before and after crazy bulk reviews how-to-lose-weight-fast best diet pills in 2022 best diet pills SARMs Online sarms for sale gnc sarms for sale SARMs Online where to buy sarms buy sarms online sarms 4 you crazy bulk review legal steroids for sale how it works legal steroids testogen gnc testogen amazon Best Sarms sarms before and after phenq gnc s4 sarm Andarine buy s4 sarm testogen gnc testogen amazon gynectrol pills gynectrol Best Sarms sarms before and after phenq gnc testoprime shark tank weight loss-drink shark tank weight loss best sarms for sale yk11 before and after yk11 before and after yk11 dosage YK11 Results yk11 sarms Best SARMS for Sale 2022 perfect keto widest choice of keto supplements keto pills shark tank keto diet pills keto diet pills best keto diet pills best legal steroids steroids for sale best-legal steroids for sale 2022 legal steroids for sale best legal steroids perfect keto diet pills perfect keto https://www.clevescene.com/cleveland/pe ... d=37758824 [url=https://www.clevescene.com/cleveland/perfect-keto-widest-choice-of-keto-supplements-perfect-keto-bhb-exogenous-keto-capsules-keto-pills-for-ketogenic/Content?oid=37758824]keto boost/url] keto supplements keto bhb pills 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".
Last edited by RayleeMarshall on Thu 02 Dec 2021 08:31, edited 4 times in total.

mrobak
Posts: 1
Joined: Thu 21 Feb 2019 10:30
Location: Ukraine
Contact:

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by mrobak » Thu 21 Feb 2019 10:43

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!

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

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by ViktorV » Fri 22 Feb 2019 12:13

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.

counter
Posts: 1
Joined: Fri 22 Nov 2019 09:15
Location: United Kingdom
Contact:

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by counter » Fri 22 Nov 2019 09:34

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

herberthaul
Posts: 1
Joined: Fri 10 Dec 2021 12:16
Contact:

Re: SSIS Execute SQL Task Error with MySQL ODBC Connection

Post by herberthaul » Mon 03 Jan 2022 13:38

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

Post Reply