How do I extract a value from SSIS package file using PowerShell?

Sql Server Integration Services packages are stored as xml files with a dtsx extension. I need to be able to extract their build numbers with Powershell. The relevant part at the top of the file lo开发者_如何学运维oks like-

<?xml version="1.0"?>

<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">

<DTS:ExecutableType="MSDTS.Package.1">

<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>

<DTS:Property DTS:Name="VersionMajor">1</DTS:Property>

<DTS:Property DTS:Name="VersionMinor">0</DTS:Property>

<DTS:Property DTS:Name="VersionBuild">265</DTS:Property>

I need to extract the VersionBuild number, but the DTS namespace is confusing me. After I do the get-content, how can I get at the value?

---

The above doesn't appear to be valid XML but assuming it is, you can use the Select-Xml cmdlet in PowerShell 2.0 to do this also:

$ns = @{ dts = 'www.microsoft.com/SqlServer/Dts' }
$xpath = '//dts:Property[@dts:Name="VersionBuild"]'
[xml](get-content file.xml) | Select-Xml $xpath -Namespace $ns | %{$_.Node}

Name                                  #text
----                                  -----
VersionBuild                          265 

---

As part of SQL Server Powershell Extensions CodePlex project there an SSIS module. Where you can do something like this:

import-module SSIS
$package = Get-ISPackage -path "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\sqlpsx1.dtsx"
$package.VersionBuild

---

Once the file is valid XML (see my comment), you can simply load it and cast to XML:

$x = [xml] (gc file.xml)

You can then access the nodes with property-like notation:

$x.Executable.Property | Where-Object { $_.Name -like 'Version*' }

Name                                                        #text
----                                                        -----
VersionMajor                                                1
VersionMinor                                                0
VersionBuild                                                265

---

Below is an example that extracts all SQL statements but it could easily be adapted to pick out any elements such as variables or connections by changing the xpath of the SelectNodes command:

$package = [xml](Get-Content 'package.dtsx')
$ns = [System.Xml.XmlNamespaceManager]($package.NameTable)
$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")

$nodes = $package.SelectNodes("//property[@name = 'SqlCommand']", $ns)

$sqls = $nodes | % {

   New-Object PSObject -Property @{
        sql = $_.'#text'
        name = $_.ParentNode.parentNode.name
   }
}

$sqls