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