Aaron Akin

February 2, 2009

Sorting SSIS Packages & Folders in SSMS

Filed under: SQL,SQL 2005,SQL 2008,SSMS — Aaron Akin @ 5:24 pm

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.

SSMS Object Explorer

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.

SSMS Object Explorer

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.

Advertisements

4 Comments »

  1. Sir i Salute

    Was following a reading SSIS “flat file upload” on expert Exchange, you are really a good man. i however wanted to find out on sending a csv to an email add on update/insert of records in an db. thanx in advance.

    Langton

    Comment by langmuch — April 23, 2009 @ 4:51 am | Reply

    • Hi Langton,

      Sorry for the delayed response. I have been in the middle of moving across country to CA and I haven’t been online as much lately.

      Let me make sure I understand you correctly. Are you wanting to send a CSV file to an email address, and when it’s received, you want SSIS to pick it up and load it into the database? Let me know if I’m wrong.

      Comment by Aaron Akin — April 30, 2009 @ 10:10 am | Reply

  2. Is there a way to list the SSIS packages order by name in BIDS via MS Visual Studio?

    thanks,
    Kitty

    Comment by Kitty Chan — June 24, 2009 @ 5:12 pm | Reply

    • Check out this link. http://www.sqldbatips.com/showarticle.asp?ID=78

      This article explains how to sort the projects in SSMS, but you can do the same thing within Visual Studio (BIDS). I used this info to create a Sort Asc button in my menu so I could click on it to reorder the projects in SSMS and VS.

      Hope this helps.

      -A

      Comment by Aaron Akin — June 25, 2009 @ 10:21 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: