sql - 从 SSIS 调用时删除语句失败

Question

我正在尝试从 SSIS 编排雪花。

我正在使用 ODBC 连接并执行 SQL 任务。截断表语句工作正常，任务成功完成。将此更改为删除，任务失败并出现以下错误:

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.

我可以从雪花查询历史记录中看到查询成功完成:



我怀疑当 SSIS 被设置为期望“无”时，“结果”看起来像一个结果集。我已将其更改为单行和“完整结果集”到一个对象中，但无论设置如何，仍然会出现错误。

我需要更改什么才能让 SSIS 成功执行针对 Snowflake 的语句？

编辑:

添加我的删除语句:

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

Answer 1

试图找出问题

在搜索这个问题时，我发现了一些有趣的东西 Devart support page报告类似问题的地方:

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.

基于 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.

这意味着当没有行匹配以下条件时，它将抛出异常(在类似情况下:ODBC 版本冲突):

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

尝试的东西

我现在无法测试此变通方法，但您可以尝试两种方法:

在删除命令后添加一个虚拟选择行

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

select 1

创建一个存储过程并将日期作为参数传递，并从 Execute SQL Task 执行它(也尝试在存储过程的末尾添加一个虚拟的 select 命令)

 Exec sp_Delete ?