Geeks With Blogs
Daniel Lackey 1.21 Gigawatts!!

I'll start this blog by posting something that bothered me for months until I found out that it was laaaazzzy stored procedure code. Have you ever looked at your SSIS packages in SSMS 2005 when connected to Integration Services only to find out that they are not in alphabetical order? How about when you are selecting a package from SQL Server when setting up a job in SQL Agent? It would help if the packages were ACTUALLY IN ORDER right?

This is no bueno....tsk tsk

To fix this tomfoolery, go to the [msdb].[dbo].[sp_dts_listpackages] stored procedure and modify it thusly:

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

Simply adding the "ORDER BY name" to the proc fixes everything. Now it looks like this one:

Now less cursing at your monitor.

Isn't it so much easier to breathe now?

Also FYI this functionality is already included (surprise, surprise) in SQL Server 2008 CTP6 and with a change to the stored procedure name to [msdb].[dbo].[sp_ssis_listpackages] .

Posted on Tuesday, September 2, 2008 10:19 PM SQL | Back to top


Comments on this post: Sort SSIS Packages in Alphabetical Order in SSMS 2005

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
great little tip, thanks!
Left by SK on Dec 26, 2008 4:00 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Do you know how to sort them in BIDS (Visual Studio)?
I have around 40-45 packages in a solution and when i open that solution in BIDS, its a big headache to find one package.

Thanks
Left by jd on Feb 02, 2009 4:05 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Yes, BIDS Helper on codexplex will install that feature as well as many others into BIDS.
Left by Huntsman85 on Mar 12, 2009 3:44 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Here is the link to BIDS Helper. The feature is "Sort Project Files".

http://www.codeplex.com/bidshelper
Left by Huntsman85 on Mar 12, 2009 4:04 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
top tip 10/10
Left by EleanorAmes on Mar 13, 2009 5:20 AM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Thanks Huntsman85! I've been meaning to post that but never got around to it. :)
Left by dlackey on Mar 13, 2009 2:36 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
what a great tip, this has bugged me for ages :)
Thanks!
Left by mcateeblack on Sep 30, 2009 10:48 AM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Thank You!!
Left by Chuck Todd on Oct 15, 2009 12:59 PM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
Both the original tip and the Huntsman85 BIDS helper tip are invaluable. Thanks
Left by PSB1 on Oct 19, 2009 6:09 AM

# re: Sort SSIS Packages in Alphabetical Order in SSMS 2005
Requesting Gravatar...
You can also modify the [dbo].[sp_dts_listfolders] in the same way with an order by on foldername. This will order your msdb folders alphabetically.
Left by Jeffrey Bender on Dec 28, 2011 3:00 PM

Your comment:
 (will show your gravatar)


Copyright © Daniel Lackey | Powered by: GeeksWithBlogs.net