Is it possible to delete an SSIS package from the MSDB database in SQL Server 2005 without DCOM rights?

Question

Unfortunately we do not have the required DCOM access to the SQL Server mentioned in this MSDN article but we do have full admin rights on the SQL Server instance.

We can therefore successfully deploy a package to the server and run it using the SQL Server agent but I cant work out how to delete a package from the server.

The only thing I can think of would be to delete the row from the dbo.sysdtspackages90 table in the MSDB database but that seems a little extreme (if not highly dangerous!) to me.

The problem lies with our server management being outsourced and we would have to fight pretty hard to get the required access - is it worth a (possibly 1 to 2 month) costly fight or can I get what we need some other way?

Answer 1

I think there is also a stored proc, sp_dts_deletepackage that you can call, if you feel deleting the row is unsafe. First call sp_dts_getfolder to get the folder ID, then pass this ID to sp_dts_deletepackage.

Another way is to configure your local SSIS service so it has a folder for this remote SQL server storage (look at MSDN for SSIS service config editing). Then you'll see these remote packages when you connect to the local SSIS service, and can delete using the UI. The service will then connect to SQL and call these stored procs for you.

Answer 2

For anyones information.

Checking what sp_dts_deletepackage actually does (right click - Modify) reveals that apart from a number of checks (security) it ends up doing:

DELETE FROM sysdtspackages90 WHERE [name] = @name AND [folderid] = @folderid AND [packageformat] = 0

No deletes or updates in any other tables