deleting catalog package using tsql and SSISDB.internal.packages table

Question

Hi,

I want to delete packages that are deployed to Catalog (SSISDB). I see lot of articles talk about deleting package from project in SSDT and publishing entire project.

I do not want to do that. Then google search showed following script

BEGIN TRAN
--SELECT *
DELETE [pkg]
FROM
[SSISDB].[internal].[projects] AS [proj] WITH (NOLOCK)
INNER JOIN
[SSISDB].[internal].[packages] AS [pkg] WITH (NOLOCK) ON
[pkg].[project_id] = [proj].[project_id]
WHERE
[proj].[name] = 'MyProject'
AND [pkg].[name] = 'MyPackage.dtsx'
-- COMMIT
-- ROLLBACK TRAN

What are the downstream effects of doing that? Not finding any information if we do this way. Does it leave any orphaned data or row in other tables. Does it affect anything else or any other packages or project

Please let me know your input.

Answer 1

Hi @Spunny

There is no official documentation said that we could delete the package in catalog via tsql.

We could not make sure that it will not affect the project.

The best way is using SSDT to remove the package from the SSIS project in the source code, and then redeploy the entire project.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Spunny,

It is not a good practice to delete individual SSIS packages.

There is a reason why modern SSIS, starting from 2012 onwards, has a Project deployment model.

SSIS packages could call child SSIS packages. So, a delete operation will cause an integrity issue.

That's why much better to create/modify/delete SSIS packages in the development environment in Visual Studio. And deploy to the SSIS run-time environment on a server the end result of it, i.e. entire SSIS project.