7

I'm trying to orchestrate Snowflake from SSIS.

I'm using an ODBC connection and the execute SQL Task. A truncate table statement works fine and the task completes successfully. Changing this to a delete, the task fails with the below error:

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.

I can see from the snowflake query history that the query completed successfully:

enter image description here

I suspected that the "results" look to SSIS like a resultset when it was set to expect "none". I've changed this to both single row and "full Result set" into an object, but still get errors regardless of the setting.

What do I need to change in order to get SSIS to execute statements against Snowflake successfully?

Edit:

Adding my delete statement:

delete from SUMMARY.Data_minutes
where date >= dateadd(day,-5  ,'2019-01-20' )
and date <= '2019-01-20'
Hadi
  • 36,233
  • 13
  • 65
  • 124
Neil P
  • 2,920
  • 5
  • 33
  • 64
  • The delete is a raw query in an Execute SQL Task yes? Do you have any parameters mapped? Does the delete work with the resultset property set to "none"? – Jacob H Mar 05 '19 at 17:46
  • What ODBC driver did you use? I suspect there could be the culprit in its implementation whereas it expects something that does not come out. I suggest you use a JavaScript UDL instead of SQL DELETE – Arthur Mar 05 '19 at 19:03
  • 1
    @NeilP what is the delete command you are using?? – Hadi Mar 05 '19 at 21:29
  • @Arthur the official Snowflake 64 bit odbc driver. – Neil P Mar 06 '19 at 09:28
  • @JacobH the delete is a raw query in the execute sql task. No parameters mapped, still fails when resultset is set to none. – Neil P Mar 06 '19 at 09:28
  • @Hadi I've added the sql query to the question – Neil P Mar 06 '19 at 09:31
  • Does Snowflake accept a `DATEADD` function? Test without the where clause. – Jacob H Mar 06 '19 at 14:18
  • @JacobH - it does. As per the question, the statement runs successfully in snowflake. The error is on the client side. – Neil P Mar 06 '19 at 14:20
  • Neil, try to run the DELETE statement outside SSIS and see if it works. If it does - you did something incompatible. Otherwise there is an issue in the driver you can raise with the provider (Snowflake). – Arthur Mar 06 '19 at 16:50
  • @Arthur it works fine outside of SSIS. – Neil P Mar 06 '19 at 16:57
  • I think i found something interesting check my answer and the link mentioned *(check the devart support team case)* – Hadi Mar 06 '19 at 17:39
  • @Hadi this is the right answer of this issue, because the OP has mentioned that no rows are affected *(number of rows deleted)*. I am wondering why you answer is ignored?? – Yahfoufi Mar 08 '19 at 09:51

3 Answers3

2

Trying to figure out the issue

While searching for this issue i found something interesting at this Devart support page where similar issue was reported:

According to Microsoft documentation 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.

Based on Microsoft documentation:

When an ODBC 3.x application calls SQLExecDirect, SQLExecute, or SQLParamData in an ODBC 2.x driver to execute a searched update or delete statement that does not affect any rows at the data source, the driver should return SQL_SUCCESS, not SQL_NO_DATA. When an ODBC 2.x or ODBC 3.x application working with an ODBC 3.x driver calls SQLExecDirect, SQLExecute, or SQLParamData with the same result, the ODBC 3.x driver should return SQL_NO_DATA.

Which means that when no rows are matching the following condition it will throw an exception (in a similar case: ODBC version conflict):

where date >= dateadd(day,-5  ,'2019-01-20' )
and date <= '2019-01-20'

Something to try

I cannot test this workarounds right now, but you can try two method:

  1. add a dummy select row after the delete command

     delete from SUMMARY.Data_minutes
 where date >= dateadd(day,-5  ,'2019-01-20' )
 and date <= '2019-01-20' 

 select 1

  2. create a stored procedure and pass the date as parameters, and execute it from the Execute SQL Task (also try to add a dummy select command at the end of the stored procedure)

      Exec sp_Delete ?
Hadi
  • 36,233
  • 13
  • 65
  • 124
2

I had the same problem, to solve I changed the connection provider from an ODBC connection to ADO.net connection.

I hope that works for you.

Bill Et Boule
  • 46
  • 5
Jeyavel
  • 2,974
  • 10
  • 38
  • 48
0

I did solve it by putting this statement (my version) as a single statement in execute sql task, placed it first in the control flow. somehow the problem was solved by doing this.

mjb
  • 41
  • 3