by Darren Green
7 Jan 2009 19:20
If you use the MSDB package store in SSIS 2005, you may have noticed that the folders are not sorted in any friendly way, it is actually just the order they were added. You end up with something like the left-hand example below. The right-hand example shows the same SSIS instance after my suggested update, and even though I only have a few folders and packages just to illustrate the point, it looks much better and easier to use.
Before | | | After |
 | | |  |
By tweaking the stored procedure used by the SSIS object model, this can easily be fixed, just run the following SQL against the (SQL Server) Database Engine instance hosting the MSDB database backing that instance of SSIS.
USE msdb
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_dts_listfolders]
@parentfolderid uniqueidentifier = NULL
AS
SELECT
folderid,
parentfolderid,
foldername
FROM
sysdtspackagefolders90
WHERE
[parentfolderid] = @parentfolderid OR
(@parentfolderid IS NULL AND [parentfolderid] IS NULL)
ORDER BY
foldername
I added the last line, the ORDER BY clause.
Some people may shy away from hacking a system stored procedure in this way, but it is not something I worry about, it is such a simple change, and clearly has minimal effect on the SSIS runtime and no impact on the Database Engine. If you want any further justification that this is a good idea, then check-out the equivalent stored procedure from SQL Server 2008 below.
CREATE PROCEDURE [dbo].[sp_ssis_listfolders]
@parentfolderid uniqueidentifier = NULL
AS
SELECT
folderid,
parentfolderid,
foldername
FROM
sysssispackagefolders
WHERE
[parentfolderid] = @parentfolderid OR
(@parentfolderid IS NULL AND [parentfolderid] IS NULL)
ORDER BY
foldername
I wrote my code first, I promise.