Sorting SSIS Packages & Folders in SSMS

It had always bothered me when I opened a SQL 2005 Integrated Services instance to find an SSIS package, only to see that the folders and packages were not in alphabetical order.

It wasn’t long before this started to get irritate me.  I did some digging and found 2 system stored procedures (sp_dts_listfolders and sp_dts_listpackages) in the msdb database that SQL Server uses to display the packages and folders in menus.

The first procedure, msdb.dbo.sp_dts_listfolders, returns the list of folders.  You can modify the procedure and add an ORDER BY clause to the end of the query, as shown below.

USE [msdb]

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

The second procedure, msdb.dbo.sp_dts_listpackages, returns the list of packages.  You can modify this procedure in the same way by adding the ORDER BY clause, as shown below.

USE [msdb]

GO



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

Now if you refresh your menu, you’ll see that everything is now ordered.

In SQL 2008, these procedures are now called msdb.dbo.sp_ssis_listfolders and msdb.dbo.sp_ssis_listpackages, and Microsoft has already added the ORDER BY clauses to both queries.