Automate deletion of SSIS packages from SSIS packagestore in SMDB

Once SSIS projects have been converted from Package Deployment to Project Deployment in Visual Studio, you may want to delete the deployed packages from the SSIS packagestore in MSDB on the SSIS servers. You can delete packages from the MSDB store one by one with SSMS. But it can be a tedious job if you have hundreds of packages on several servers (Development, Testing, Production environment). We can generate a script to delete the packages with the DTUTIL utility. 

Here is a very simple example

Before:

• SSIS project in Visual Studio in package deployment mode
• 2 packages PackageA & PackageB
• SSIS Servers (development and production)
• MSDB Package store

After:

• SSIS project in Visual Studio in project deployment mode
• 2 packages PackageA & PackageB

• SSIS Servers (development and production)
• SSISDB Integration Services catalog

Once the packages have been deployed in the SSISDB catalog, there is no need to keep them any longer in the MSDB Package store.

The following SQL script will generate a list of all packages in a given subdirectory and generate the SQL script to delete all of them. 

declare @SsisServers varchar(255);
declare @Parentfolder varchar(255);
declare @subfolders varchar(255);
declare @DeleteSubfolder bit;
SET @SsisServers = 'ServerDEV;ServerTST;ServerPRD';
set @Parentfolder = 'Genledger';
set @subfolders = 'Project1;Project2';
set @DeleteSubfolder = 1;
DECLARE @ts TABLE ( SsisServer varchar(255) );
insert into @ts SELECT * FROM STRING_SPLIT (@SsisServers, ';');
DECLARE @tf TABLE ( subfolder varchar(255) );
insert into @tf SELECT * FROM STRING_SPLIT (@subfolders, ';');
SELECT os.script
FROM @ts AS ts
OUTER APPLY (
SELECT  o.script
FROM    @tf AS t
        OUTER APPLY ( 
SELECT ('DTUTIL "/SQL ' +  @Parentfolder + '\' + t.subfolder + '\' + p.[name] + ' /DELETE /SOURCESERVER ' + ts.SsisServer +'"') AS script
FROM msdb.dbo.sysssispackages p
INNER JOIN msdb.dbo.sysssispackagefolders f
ON f.folderid = p.folderid
INNER JOIN msdb.dbo.sysssispackagefolders pf
ON f.parentfolderid = pf.folderid
WHERE pf.foldername =@Parentfolder
and f.foldername =t.subfolder
                    ) o
) os;
If @DeleteSubfolder <> 0
SELECT ('DTUTIL "/SOURCESERVER "' + ts.SsisServer + '" /FDELETE SQL;' +  @Parentfolder + ';' + t.subfolder) AS script
FROM @tf t
cross join @ts ts
go

The output of the script can then be copied into a Powerquery script:

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerPRD"

#delete subfolder

DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project2"

DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project2"

DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project2"

Run the script from the Powershell ISE

That's all folks!