SQLTeam.com | Weblogs | Forums

Getting error when delete the able donot have data

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 :scriptis: 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 donot 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.

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



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

when emp table donot have data: if I run delete query its working fine without error and same query running using execute sql task its failed.could you please help on it

why the double-quotes around the schema name? Is that a postgres thing?

Can you modify that command to use TRUNCATE instead?

https://www.postgresql.org/docs/9.5/sql-truncate.html

If not - then what is the exact command you are trying to execute? There are additional qualifiers that you may need to include depending on the structure of the table.

some time we need to delete specific data only. like delete from emp where empid=10
if empid=10 not exist the package is failed. if i run same query in postgres then its working.
please tell me any alternative solution

Please show us the whole error?

SQL Task] Error: Executing the query "delete from emp
usin..."

I suspect what you are assuming is different than what is actually happening.

Also what I am seeing you are using delete from temp.emp with no where clause which will wipe out the whole table.
Also what about other tables that might have dependencies to this table?