SQL Server: Ordering SSIS Packages and Folders in SSMS 2005

You may have noticed that Integration Service in SSMS 2005 does not list SSIS packages and Folders in any specific order. It’s random, most likely in the order we have added them in SSIS. Some times its very frustrating to search for a specific package or folder as the list is not ordered. Fortunately this issue does not exist with SQL Server 2008.

To fix this issue with SQL Server 2005, we need to alter two system SPs and these are msdb.dbo.sp_dts_listpackages and msdb.dbo.sp_dts_listfolders. 

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 –- Added to fix Package’s order
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 –- Added to fix the folder’s order

Regards

Rakesh Mishra

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook