Extract a value from a dtsx file with Powershell

Question

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 looks 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?

Answer 1

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

Answer 2

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 

Answer 3

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