getting error when delete and the table does not have data

0
votes

Hi I have one doubt in ssis

I want delete the data in the target server(postgres) tables data using ssis package.

  • database: postgres server
  • Table: emp
  • in execute sql task, script is: delete from emp and connection used odbc

When I run the executesql task in ssis package if emp table have data then its working fine and I am getting the error when emp table does not have data.

[Execute SQL Task] Error: Executing the query "delete from emp usin..." 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.

enter image description here

enter image description here

suppose if i run same query in pgadmin tool that time it is not getting any error even though emp table does not have data.

could you please tell me how to avoid this issue in ssis package

1
postgresqlssisssis-2012postgresql-9.4
Can you tell us what version of VS and SSDT you are using. I assume it is not an issue with your code, but the libraries used by Visual Studio. - Gabriel Durac
ssdt: Microsoft SQL Server 2016 (SP1) vs: Microsoft Visual Studio 2015 Shell (Integrated) - harnithu

1 Answers

1
votes

In Execute SQL Task component , you should type your delete script in transaction block like below ;

BEGIN;
DELETE FROM yourtable;
COMMIT;