Wednesday, April 16, 2008

Show SSIS Packages Alphabetically in SSMS

Trying to find an SSIS package in SQL Server Management Studio is difficult because the packages are ordered by the date they were imported. You can make a quick adjustment to a system stored procedure, which will show the packages in alphabetical order.

Modify the [msdb].[dbo].[sp_dts_listpackages] stored procedure to include an order by at the end:

ALTER PROCEDURE [dbo].[sp_dts_listpackages]
@folderid uniqueidentifier
AS
SELECT
name,
id,
description,
createdate,
folderid,
datalength(packagedata),
vermajor,
verminor,
verbuild,
vercomments,
verid
FROM
sysdtspackages90
WHERE
[folderid] = @folderid
ORDER BY

name

and Voila! your packages will be listed in alphabetical order when you view them in SSMS.

As of SQL Server 2008 CTP6, this functionality has already been added. The new stored procedure that is executed when expanding the list of packages in SSMS is named: [msdb].[dbo].[sp_ssis_listpackages].

Versions: SQL Server 2005 SP2/SQL Server 2008 CTP6

1 comment:

Anonymous said...

Perfect - Thank you so much!