CODEWITHSUNDEEP
sqlsql-serverssisetlexecute-sql-task
Eva Donaldson
Eva Donaldson

Reputation: 405

SSIS truncate table fails; delete from succeeds

I have an SSIS package where I want to clear a staging table and repopulate it. I have created an Execute SQL Task of TRUNCATE TABLE TABLE_NAME that fails with the error:

Table does not exist or you do not have permission

If I alter the task to do a DELETE FROM TABLE_NAME it works like a charm. If I log in as the user I am connecting with and execute TRUNCATE TABLE TABLE_NAME, it also works like a charm. Can anyone help me understand why I cannot execute the truncate in the SSIS job, but I can as the user? Because, I would much prefer to truncate the table rather than do a delete.

Upvotes: 4

Views: 1690

Answers (1)

Hadi
Hadi

Reputation: 37313

You can split the error into two parts:

  1. Table does not exist
  2. Do not have permission

So if you are sure that the table exists (As you say that DELETE operations works well), it is a permission issue, check the database administrator for that maybe you don't have a permission to truncate data from this table.

Note that TRUNCATE TABLE requires more permissions then DELETE operations. Based on the official documentation:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

On the other hand, the DELETE operation requires less permissions. Based on the official documentation:

DELETE permissions are required on the target table. SELECT permissions are also required if the statement contains a WHERE clause.

DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

Upvotes: 2

Related Questions