10

Seems it could not be done in SSMS. Does Microsoft actually provide this option for users to delete single package within SSIS catalog? Thanks.

Best Regards, Mike

OysterSing
  • 165
  • 1
  • 1
  • 12

4 Answers4

3

Microsoft has added Incremental package deployment in SQL 2016 - This feature lets you deploy one or more packages to an existing or new project without deploying the whole project.

As Incremental package deployment has been added, I believe, decremental for single package is not there. There are no any SPs other than catalog.delete_project, Folder and Environments in SSIS catalog (Projects and Packages).

p2k
  • 2,126
  • 4
  • 23
  • 39
2

What I did is this:

I looked for the project_ID from [SSISDB].[internal].[projects]. We have Dev/QA and UAT packages deployed on same server under different projects, which is why I looked for the project_ID.

Project DB sanpshot

Then I just ran the delete script on [SSISDB].[internal].[packages]

 delete from [SSISDB].[internal].[packages] where project_id=3 and name like '%your package name%'
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Manish
  • 59
  • 2
  • For SQL Server 2016-2022. Deletion of the latest package version record from [SSISDB].[internal].[packages] does remove package from Object Explorer, but DOES NOT remove it from SSISDB. This can be checked by exporting project, "deleted" package will be present in *.ispac file among other packages. If new single package deployed to the project, "deleted" package appears again. When a single package deployed, a new project version is created under the hood and stored in [SSISDB].[internal].[object_versions] in varbinary format. SSISDB does not store code of a separate *.dtsx packages. – Vitalii Feb 23 '23 at 23:05
1

By definition, no. The SSIS db catalog uses a new project-based deployment model that encapsulates your entire project in a ZIP archive and deploys that archive to the server (called an .ispac). See Deploy Integration Services for more info.

At best you could delete the current project and deploy an updated one using catalog.delete_project and catalog.deploy_project.

Kittoes0124
  • 4,930
  • 3
  • 26
  • 47
1

Wrapped it in a TRAN

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

Buck
  • 11
  • 1