A while ago I posted a query to create a list of all the Integration Services packages deployed to the MSDB. I am now using that query to take it a step further.
If you’ve been using SSIS for a while you’ve probably noticed that the Management Studio doesn’t like to delete Integration Services folders that are not empty. In fact, it will politely ask you if you’re sure that you want to delete the folder on which you’ve just selected the “Delete” option through the right-click menu.
So you click the Yes button. But then it shows you the following message:
SSIS folder ‘FolderWithSubfolders’ contains packages and/or other folders. You must drop these first. (Microsoft SQL Server Native Client 10.0)
Graphically it looks like this:
And this message can be really annoying if you’ve got a main folder with, let’s say, five subfolders, and each subfolder contains about 20-30 packages. If you want to delete this folder you first need to delete each package separately and then delete the five subfolders, and then you can finally delete the main folder. And all that through the right-click pop-up menu because you can’t just select the object in the Object Explorer and hit the Delete button on the keyboard – it doesn’t have an action on SSIS objects…
So, I wasn’t planning on doing such a job manually and came up with the following stored procedure.
It’s probably a bit long but don’t run away just yet, I will explain what’s going on down below the code, and there are some comments in the code as well.
/*
DESCRIPTION: Deletes all folders and packages under, and including, specified folder.
WRITTEN BY: Valentino Vranken
CREATED: 2010-02-28
VERSION: 1.0
USAGE:
-- mind the forward slash
EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'
-- to delete a subfolder
EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders/ASubfolderWithPackages'
COPIED FROM: http://blog.hoegaerden.be
Note 1: folder names are not case-sensitive
Note 2: uses system tables and (undocumented) stored procedures located in MSDB.
Note 3: this code was written for SQL Server 2008. For 2005:
o sysssispackagefolders -> sysdtspackagefolders90
o sysssispackages -> sysdtspackages90
o sp_ssis_deletefolder -> sp_dts_deletefolder
o sp_ssis_deletepackage -> sp_dts_deletepackage
*/
CREATE PROCEDURE dbo.SSIS_RecursiveDeleteFolder
@Folder varchar(2000)
AS
BEGIN
set nocount on;
declare @foldersToDelete table
(
folderid uniqueidentifier,
Lvl int
);
declare @packagesToDelete table
(
PackageName sysname,
folderid uniqueidentifier,
Lvl int
);
--retrieve list of folders to be deleted
with ChildFolders
as
(
select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
cast('' as sysname) as RootFolder,
cast(PARENT.foldername as varchar(max)) as FullPath,
0 as Lvl
from msdb.dbo.sysssispackagefolders PARENT
where PARENT.parentfolderid is null
UNION ALL
select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
case ChildFolders.Lvl
when 0 then CHILD.foldername
else ChildFolders.RootFolder
end as RootFolder,
cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
as FullPath,
ChildFolders.Lvl + 1 as Lvl
from msdb.dbo.sysssispackagefolders CHILD
inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
insert into @foldersToDelete
select F.folderid, F.Lvl
from ChildFolders F
where F.FullPath like @Folder + '%';
--retrieve list of packages to be deleted
with ChildFolders
as
(
select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
cast('' as sysname) as RootFolder,
cast(PARENT.foldername as varchar(max)) as FullPath,
0 as Lvl
from msdb.dbo.sysssispackagefolders PARENT
where PARENT.parentfolderid is null
UNION ALL
select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
case ChildFolders.Lvl
when 0 then CHILD.foldername
else ChildFolders.RootFolder
end as RootFolder,
cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
as FullPath,
ChildFolders.Lvl + 1 as Lvl
from msdb.dbo.sysssispackagefolders CHILD
inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
insert into @packagesToDelete
select P.name, F.folderid, F.Lvl
from ChildFolders F
inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
where F.FullPath like @Folder + '%';
--use cursor to loop over objects to be deleted
declare objectsToDelete_cursor cursor
for
select P.folderid, P.Lvl, P.PackageName, 'P' as ObjectType
from @packagesToDelete P
UNION ALL
select F.folderid, F.Lvl, null, 'F'
from @foldersToDelete F
order by Lvl desc, ObjectType desc;
open objectsToDelete_cursor;
declare @folderid uniqueidentifier;
declare @lvl int;
declare @packageName sysname;
declare @objectType char;
fetch next from objectsToDelete_cursor
into @folderid, @lvl, @packageName, @objectType;
while @@FETCH_STATUS = 0
begin
if @objectType = 'F'
begin
print 'exec msdb.dbo.sp_ssis_deletefolder '
+ cast(@folderid as varchar(max));
exec msdb.dbo.sp_ssis_deletefolder @folderid;
end
else
begin
print 'exec msdb.dbo.sp_ssis_deletepackage '
+ @packageName + ', ' + cast(@folderid as varchar(max));
exec msdb.dbo.sp_ssis_deletepackage @packageName, @folderid;
end
fetch next from objectsToDelete_cursor
into @folderid, @lvl, @packageName, @objectType;
end;
close objectsToDelete_cursor;
deallocate objectsToDelete_cursor;
END
Before trying to dismantle this stored procedure, I recommend you to read my previous article on retrieving the list of packages. That already explains half of the code, if not 75%.
Our mission is to find a way to recursively delete packages and folders contained in a specified folder. To be able to loop over those objects in the correct order (from the deepest level up until the level of the folder specified), the SP creates two table variables: one to hold all folders under the specified folder (@foldersToDelete) and one to hold the packages under the specified folder, including all subfolders (@packagesToDelete).
Based on those two lists I create a cursor that joins these two together, taking their level and object type into consideration. That’s important because we first need to delete the packages in the lowest level folder, followed by their containing folder, then move one level up and do the same.
We then use the cursor to loop over the packages and folders and use two undocumented system stored procedures – one for each object type- to delete the package or folder. These system SPs are located in the MSDB. Here’s how they are defined:
ALTER PROCEDURE [dbo].[sp_ssis_deletefolder]
@folderid uniqueidentifier
AS
ALTER PROCEDURE [dbo].[sp_ssis_deletepackage]
@name sysname,
@folderid uniqueidentifier
AS
As you can see, the parameters for these procedures are not that complicated. Both of them expect a uniqueidentifier as identification for the folder. That’s okay, these IDs are stored in the msdb.dbo.sysssispackagefolders table and retrieved by our queries to create the list of to-be-deleted objects.
Furthermore, the sp_ssis_deletepackage SP expects the name of the package to be deleted. Not a problem either, those names are obtained from the msdb.dbo.sysssispackages table.
Note for SQL Server 2005 users: this code was written for SQL Server 2008. The system stored procedures and system tables exist in 2005 as well, but they have different names. See the comment header of my SP for more details.
So, let’s give it a little test. Following screenshot shows the setup. What I will do is use the stored procedure to delete the FolderWithSubfolders folder. If you’ve been paying close attention, that is the same folder which I tried to delete manually through the Management Studio’s right-click menu (see first screenshot above).
After creating the SP, I ran following command:
EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'
And that gave me the following output in the Messages pane:
exec msdb.dbo.sp_ssis_deletepackage AnotherPackage, 7F38288D-4370-40A8-80E3-E92283033E4C
exec msdb.dbo.sp_ssis_deletepackage Package, 7F38288D-4370-40A8-80E3-E92283033E4C
exec msdb.dbo.sp_ssis_deletefolder 4102ED59-ED75-4D93-BBAE-0A162447BF02
exec msdb.dbo.sp_ssis_deletefolder 7F38288D-4370-40A8-80E3-E92283033E4C
exec msdb.dbo.sp_ssis_deletefolder C156B436-8C78-4BF9-99F9-5ABFAB10C405
I have deliberately put a couple of print commands in the stored procedure to dump the commands that are actually being executed. This gives us a good idea of what’s going on.
That’s it for now folks. Thank you for reading this, and if you found it useful or you’ve got some questions about it: post a comment!
Have fun!
Valentino.
