I am new to Powershell and I am trying to remove a number of SSIS packages that are no longer in use from the remote server. Some are in SSISDB and some are in MSDB. I am trying to create a PowerShell script that checks where(MSDB/SSISDB) the package is and deletes it. Plus, I need to run my SQL query that drops the objects related to that package using PS. Can anyone explain how that is done with a sample PowerShell script?

CodePudding user response：

MSDB

Depends on how you want to remove the package. Do you want to invoke an external process (dtutil.exe) or do you want to build/issue SQL queries?

Command line

dtutil.exe is the command line utility for interacting with the MSDB and has been available since the 2005 release.

Sample deletion would look like

dtutil.exe /SQL PackageToBeDeleted /DELETE

Stored Procedure

sp_dts_deletepackage (2005) /sp_ssis_deletepackage (2008 ) takes as an argument the package name and the folder guid.

-- Delete a package in the root folder
EXECUTE msdb.dbo.sys_ssis_deletepackage 'MyPackage', '00000000-0000-0000-0000-000000000000';

Older question on how to thread the dts named versions of the metadata to build deletes

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

Delete

You can also just delete directly from the table.

-- SQL Server 2005
DELETE P FROM dbo.sysdtspackages90 AS P WHERE P.name = 'mypackage'; 
-- SQL Server 2008 
DELETE P FROM dbo.sysssispackages AS P WHERE P.name = 'mypackage';

SSISDB

You can't delete an SSIS package from the SSISDB. Packages don't exist in the SSISDB, Folders contain Projects which happen to have Packages (and metadata and project parameters and optionally project connection managers). That is all a deployable unit, with an .ispac extension when stored to disk.

Removal of a project is through the stored procedure delete_project

EXECUTE SSISDB.catalog.delete_project
    @folder_name = 'MyFolder'
,   @project_name = 'MyProject';