WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Friday, April 8, 2011

SSIS Package Versions with Powershell

If you have a larger SSIS implementation and you want to keep track of versions of the packages stored on a server, this script will help.  I thought I had found the base portion of this script on the web and then added to it, but I cannot find the source at this point.  I will only take credit for adding on to it.

It simply loops through a file structure and gives you the package name along with the Version attributes from the package.  Also shows how to read XML in Powershell.  Enjoy!



$BaseFolder = "C:\SSIS\Packages\"
$SSISPkgs
= Get-ChildItem -path $BaseFolder -recurse -include *.dtsx |
                               
Select-Object Directory,Name

$ns
= @{ dts = 'www.microsoft.com/SqlServer/Dts' }
$build
= '//dts:Property[@dts:Name="VersionBuild"]'
$vMajor
= '//dts:Property[@dts:Name="VersionMajor"]'
$vMinor
= '//dts:Property[@dts:Name="VersionMinor"]'

foreach
($pkg in $SSISPkgs)
{

       [
string]$SSISPkgPath = $pkg.Directory.ToString().Trim() +'\'+ $pkg.Name.ToString().Trim()

       
$BuildNode= [xml](get-content $SSISPkgPath) | Select-Xml $build -Namespace $ns | %{$_.Node}
       
$VersionMajor= [xml](get-content $SSISPkgPath) | Select-Xml $vMajor -Namespace $ns | %{$_.Node}
       
$VersionMinor= [xml](get-content $SSISPkgPath) | Select-Xml $vMinor -Namespace $ns | %{$_.Node}
       
Write-host $SSISPkgPath " - Version: " $BuildNode.get_InnerXml() " Major:" $VersionMajor.get_InnerXml() " Minor:" + $VersionMinor.get_InnerXml();
}

4 comments:

  1. I don't know a whole lot about PowerShell, but I think you need to change
    Write-host $SSISPkgPath " - Version: " $node.get_InnerXml() " Major:" $VersionMajor.get_InnerXml() " Minor:" + $VersionMinor.get_InnerXml();
    to
    Write-host $SSISPkgPath " - Version: " $BuildNode.get_InnerXml() " Major:" $VersionMajor.get_InnerXml() " Minor:" + $VersionMinor.get_InnerXml();

    Still, only took me about 3 minutes to figure that out, versus the hour it probably would have taken to write this script.

    ReplyDelete
  2. Thanks for pointing out the typo, it has been fixed.

    ReplyDelete
  3. Hi WIDBA, have you got any idea how to update VersionBuild, VersionMajor,
    VersionMinor via powershell?
    Thanks,
    Kris

    ReplyDelete
    Replies
    1. I have never done it, but it should be pretty straightforward.

      This article does a nice job of explaining the concept. Use that coupled with the XML paths above and you should be good to go.

      http://powershell.com/cs/blogs/tobias/archive/2009/02/02/xml-part-2-write-add-and-change-xml-data.aspx

      Delete