How do I extract a value from SSIS package file us

问题:

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?

回答1:

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

回答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 

回答3:

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

回答4:

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