SSISDB Delete all packages and environments

SSISDB tear down script

For my Summit 2015 presentation, 2014 SSIS Project Deployment Model: Deployment and Maintenance, I needed to revise my SSISDB tear down script. When I first built it, it removed all the projects and then removed all the folders. Which was great but as I've noted elsewhere, a folder can contain Environments and those too will need to be accounted for. Otherwise, the catalog.delete_folder operation will fail as it is not empty.

Running the following code will remove everything in your SSISDB. This is the nuclear option so be certain you really want to clean house. You can uncomment the WHERE clause and selectively remove folders for a tactical nuclear option.

How it works is simple: I query catalog.folders to get a list of folders and then look in catalog.projects to find all the projects contained within the folder and delete those. I then repeat the process but look in catalog.environment to identify and remove all the SSIS environments.

USE [SSISDB]
GO

DECLARE
    @folder_name nvarchar(128)
,   @project_name nvarchar(128)
,   @environment_name nvarchar(128);

DECLARE Csr CURSOR
READ_ONLY FOR 
SELECT
    CF.name AS folder_name
FROM
    catalog.folders AS CF
--WHERE
--    CF.name IN ('');
;

OPEN Csr;
FETCH NEXT FROM Csr INTO
    @folder_name;
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

        -------------------------------------------------------------
        -- Drop any projects
        -------------------------------------------------------------
        DECLARE FCsr CURSOR
        READ_ONLY FOR 
        SELECT
            CP.name AS project_name 
        FROM
            catalog.projects AS CP
            INNER JOIN
                catalog.folders AS CF
                ON CF.folder_id = CP.folder_id
        WHERE
            CF.name = @folder_name;

        OPEN FCsr;
        FETCH NEXT FROM FCsr INTO
            @project_name;
        WHILE(@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE catalog.delete_project
                @folder_name
            ,   @project_name;

            FETCH NEXT FROM FCsr INTO
                @project_name;
        END
        CLOSE FCsr;
        DEALLOCATE FCsr;

        -------------------------------------------------------------
        -- Drop any environments
        -------------------------------------------------------------
        DECLARE ECsr CURSOR
        READ_ONLY FOR 
        SELECT
            E.name AS project_name 
        FROM
            catalog.environments AS E 
            INNER JOIN
                catalog.folders AS CF
                ON CF.folder_id = E.folder_id
        WHERE
            CF.name = @folder_name;

        OPEN ECsr;
        FETCH NEXT FROM ECsr INTO
            @environment_name;
        WHILE(@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE catalog.delete_environment             
                @folder_name
            ,   @environment_name;

            FETCH NEXT FROM ECsr INTO
                @environment_name;
        END
        CLOSE ECsr;
        DEALLOCATE ECsr;

        -------------------------------------------------------------
        -- Finally, remove the folder
        -------------------------------------------------------------
        EXECUTE [catalog].[delete_folder]
            @folder_name;

    END
    FETCH NEXT FROM Csr INTO
        @folder_name;

END

CLOSE Csr;
DEALLOCATE Csr;

Caveat

The one thing I haven't investigated yet was cross folder dependencies. Imagine folders Configurations and Projects. Configurations has an Environment called Settings. Projects has a project called AWShoppingCart which then has a reference to the environment Settings. I expect I will be able to delete the Configurations folder and the environment just fine and it will just leave the project AWShoppingCart broken until I reconfigure it. But, the environment delete operation could just as easily fail if there's reference count is non-zero.