Sorting MSDB package folders

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

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.

Comments (6) -

1/20/2009 12:00:21 PM #

pjones

Then modify procedure sp_dts_listpackages and add ORDER BY [name] to get the packages in order within the folder.

pjones United Kingdom

1/20/2009 4:33:10 PM #

Michael Baggot

Thanks that works great. Does anyone have a script to put the packages in alphabetical order?

Michael Baggot

1/21/2009 8:51:19 PM #

Chris Schmidt

To get the packages in order, I've added a clustered index to the dbo.sysdtspackages90 table on the name field and put it in ascending order.  

Chris Schmidt United States

1/22/2009 1:08:41 PM #

Allan Mitchell

Chris

A query with no ORDER BY clause is never guaranteed to return in CI order.  Granted it very often does but there is no guarantee.

Allan Mitchell Germany

2/2/2009 1:07:45 AM #

Thirumala

Here is the script in msdb.. my current version is 10.0.1600..
Just wondering why do i need to chagne anything...

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  

Thirumala United States

2/2/2009 1:31:02 PM #

Darren Green

Thirumala, this article is about SSIS 2005, and how to add sorting to the MSDB view of packages. The equivalent stored procedure from SQL Server 2008 is only quoted to show that it has already been done, and why this is a good idea for 2005.

Darren Green United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading