Ordering SSIS packages in Visual Studio 2005 (part II)
Ok the first post on Ordering SSIS packages in Visual Studio received a large number of hits...no comments but a good number of hits so I figured a follow up was due. After my first post I found myself ordering all of my SSIS packages with the order-ssisProj.ps1 code but I found it to be a bother since I had to put in the location of each .dtproj file. So with this second post I will introduce a script I wrote a few days ago that orders all of my SSIS packages. It finds any .dtproj file in the default USERPROFILE + \my documents\ and orders them. Before I get into the powershell script a quick intro about running a powershell script. I'm not going to go into great detail other than to say you have to enable scripting in powershell. Second you have to take the code below and put it into a text file and save it with a .ps1 extension. (As always you use this script at your own risk...and you should always understand what code you grab off a website is doing)
order-SSISproj2.ps1
#-----------------------------------------------------------------------------
#
#SFIBICH
#5/18/08
#Version 1.0
#order-SSISproj.ps1
#This script saves a backup file as a .bak
#This script will search the USERPROFILE enviorment variable + My documents
#as its starting place for .dtproj files
#This script allows args that will replace the default search path
#Use at your own risk, no guarantees
#-----------------------------------------------------------------------------
#
#$ars[0] - relative path to the file name
#$a = path to this file when executed
#$c = counter variable
#$i = counter for progress bar
#$x = xml object varaible
#$y = list of all dtproj files
#$z = list of dtspackages node items inside the xml document
#
#-----------------------------------------------------------------------------
$a=$^
$i=0
if ($args[0].length -gt 0) {
$fileLocation=$args[0]
if ($fileLocation -eq '?') {
get-content $a | ? {$_.length -gt 0} | ? {$_.substring(0,1) -eq '#' }
exit
}
else {
date;$y=gci -path ($fileLocation) -include *.dtproj -recurse;date
}
}else{
write-warning('starting search for *.dtproj files at '+$x.value+'\My Documents\ this is a recursive search and may take a while')
date;$y=gci -path ($x.value+'\My Documents\') -include *.dtproj -recurse;date
}
$y | % {
$i+=1
write-progress "re-ordering dtproj files- progress" "% complete" -perc ($i/$y.length*100)
$_.Name
[xml]$x = get-content $_.FullName
$x.save($_.FullName+'.bak')
$z=$x.project.dtspackages.dtspackage | sort-object -property name
$c=0
$z| % {if ($c -eq 0) {$c+=1} else {$x.project.dtspackages.RemoveChild($_)}} | out-null
$z| % {if ($c -eq 1) {$c+=1} else {$x.project.dtspackages.AppendChild($_)}} | out-null
$x.save($_.FullName)
}
order-SSISproj2.ps1 (the walk thru)
Ok the first few lines 1 thru 22 are standard documentation. This will be use later and is useful to anyone who wants to update or modify the code in the future. Lines 23 grabs the first token of the previous command line (The file name in this case) and assigns it to the variable $a. Line 24 sets $i to a default value of 0. Lines 24 thru 34 parse the arguments passed to the script to figure out first if there are any arguments and if they are what are they. If there where arguments passed to the script and they happen to be '?' (the help command) then the first argument of the previous commands contents are read. (basically go read the file that was entered into the command) Sending on the lines with comments to the screen for ouptut. If the arguments passed to the script are not '?' then it is assumed that it is a starting location for a search for *.dtproj files. That directory and every directory inside it are searched for proj files and assigned to the array $y. Else the default search path is searched for *.dtproj files and those locations are assinged to the array $y. Lines 38 thru 49 is where the bulk of the work happen. Line 38 executes a for each on the $y array looping through the code block line 39 to 49. Line 39 increments the $i variable so we can show progress. Line 40 brings up the progress bar in powershell and shows the percent complete incrementing with each loop through this code block. Line 41 writes the name of the file the script is working on to standard output. Line 42 gets the content of the dtproj file for this loop and places it into a XML casted variable $x. Now that line of code is not mine, I can't remember where I saw it but it is very slick! Now I have an XML object to work with full of data! First thing I do is to save the xml file with the same file name and a.bak extension just in case I mess something up. Line 43 grabs all of the nodes in the project.dtspackages node and places them sorted into the $x variable. In line 45 I set the $c variable to 0 so I can check this later to know how many times I have gone thru inner loops. Line 46 removes all except for the first dtspackage node objects, based on the ordered set in $x. I had to do this because when I removed all of the nodes the parent dtspackages node no longer existed and I couldn't add anything back to it. Line 47 adds all of the dtspackage nodes back in the sorted order. Line 48 saves the file and then line 49 you loop back to start the next file. That’s it short and sweet!
Ok and here is the before and after:
Before (notice the XX_XXX_DX packages at the bottom)
After (notice that the XX_XXX_DX packages are in the correct location)
Once again I am not any way a powershell expert but I wanted to share a useful script that I developed for my own use with anyone who is working in an environment with a large number of SSIS packages in a single project or solution. Let me know your thoughts? If you have an improvement to the script send it to me or post it!