Ordering SSIS packages in Visual Studio 2005
Problem: SSIS Packages in Microsoft Visual Studio 2005 are ordered in the order of package creation by default, or more precisely the order they have been added to that solutions/projects folder. What this means is that if you have an SSIS project that has more than a few SSIS packages let’s say 70+ it becomes an issue when you go to look for a particular package. You can't just have a good naming scheme for your packages and hope to find them quickly (though that does help). Because the packages are not ordered in alphabetical order and MSVS 2005 does not give you a way to change the default order easily you are stuck searching through what is pretty much an un-ordered list. Ok so if you have encountered this problem you know it’s real pain to deal with.
The Answer: Visual Studio uses metadata in an xml file to know which packages are in your project and what you want these packages to be called. Note: The file names and the package names in the project do not have to be the same but they are by default. This file is the [ProjectName].dtproj file located in the root of the project folder for any given SSIS project.
My Solution: Powershell to the rescue
#------------------------------------------
#
#SFIBICH
#5/16/08
#Version 1.0
#order-SSISproj.ps1
#This script saves a backup file as a .bak
#------------------------------------------
#$ars[0] - relative path to the file name
#$c = counter variable
#$x = xml object varaible
#------------------------------------------
if ($args[0].length -gt 0) {
$fileLocation=$args[0]
[xml]$x = get-content $fileLocation
$x.save($fileLocation+'.bak')
$y=$x.project.dtspackages.dtspackage | sort-object -property name
$c=0
$y| % {if ($c -eq 0) {$c+=1} else {$x.project.dtspackages.RemoveChild($_)}}
$y| % {if ($c -eq 1) {$c+=1} else {$x.project.dtspackages.AppendChild($_)}}
$x.save($fileLocation)
}else{
write-warning('Please enter the location of a SSIS project .dtproj file (NO WORK EXECUTED)')
}
Thats it I hope some of you find this useful!