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 TwitterFollow me on FaceBook

3 Comments on “SQL Server: Ordering SSIS Packages and Folders in SSMS 2005”

  1. Very useful it is Thanks Rakesh.

    One prob: I am getting the following error when I try to connect to ssis server from ssms

    Connect to SSIS Service on machine “localhost” failed: The specified service does not exist as an installed service.

  2. Hi Rupesh,

    Please verify that SSIS is running on your machine. You can verify this from either from Admin Tools->Services or SQL Server Configuration Manager.

    HTH,
    Rakesh

Leave a Reply

Your email address will not be published. Required fields are marked *