Delete statement fails when called from SSIS

Question

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:

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' 

Answer 1

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 ?