PowerShell从SSIS包XML提取连接字符串失败，求排查方案

需要批量遍历指定文件夹下的SSIS包（.dtsx文件），提取其中的SQL语句和连接字符串。当前PowerShell脚本能正常提取SQL语句，但连接字符串始终无法获取到结果，需要排查问题并实现批量自动化操作。

原脚本代码 #

$folderPath = "Z:\Database\SSIS\Test"
$namespace = @{
    DTS = "www.microsoft.com/SqlServer/Dts"
    SQLTask = "www.microsoft.com/sqlserver/dts/tasks/sqltask"
    DTSConnection = "www.microsoft.com/SqlServer/Dts/Connection"
}

# Get all files in the folder with the .dtsx file extension
$packageFiles = Get-ChildItem -Path $folderPath -Filter "*.dtsx" -Recurse

foreach ($file in $packageFiles) {
    Write-Output "Processing file $($file.FullName)"

    # Load the package XML file
    $xml = [xml](Get-Content $file.FullName)

    # Extract the SQL statement from the package XML
    $sqlStatementSource = Select-Xml -Xml $xml -Namespace $namespace -XPath "//DTS:ObjectData/SQLTask:SqlTaskData/@SQLTask:SqlStatementSource" | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value

    # Extract the connection string from the package XML
    $connectionString = Select-Xml -Xml $xml -Namespace $namespace -XPath "//DTS:ObjectData/DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager/@DTSConnection:ConnectionString" | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value

    Write-Output "SQL Statement: $sqlStatementSource"
    Write-Output "Connection String: $connectionString`n"
}

关联XML示例 #

<DTS:ConnectionManagers>
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Test]" DTS:CreationName="OLEDB" DTS:DTSID="{0C0B353E-C2D5-4CEE-B6D0-8F97B030C8F4}" DTS:ObjectName="Test">
<DTS:ObjectData>
<DTS:ConnectionManager DTS:ConnectRetryCount="1" DTS:ConnectRetryInterval="5" DTS:ConnectionString="Data Source=Test\PROD;Initial Catalog=MM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"/>
</DTS:ObjectData>
</DTS:ConnectionManager>
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Flat File Connection Manager]" DTS:CreationName="FLATFILE" DTS:DTSID="{0B7B5750-8D58-4455-A008}" DTS:ObjectName="Flat File Connection Manager">
<DTS:ObjectData>
<DTS:ConnectionManager DTS:Format="Delimited" DTS:LocaleID="6153" DTS:HeaderRowDelimiter="_x000D__x000A_" DTS:RowDelimiter="" DTS:TextQualifier="_x003C_none_x003E_" DTS:CodePage="1252" DTS:ConnectionString="\\galbswebwp00002\DatawatchDropZone\MBUD_MONTHLYSHIP.TXT">
<DTS:FlatFileColumns>
<DTS:FlatFileColumn DTS:ColumnType="Delimited" DTS:ColumnDelimiter="_x000D__x000A_" DTS:MaximumWidth="2000" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="Column 0" DTS:DTSID="{C95AF712-EDC8-40DB-B031-D223FCEDD8E5}" DTS:CreationName=""/>
</DTS:FlatFileColumns>
</DTS:ConnectionManager>
</DTS:ObjectData>
</DTS:ConnectionManager>
</DTS:ConnectionManagers>

问题原因分析 #

1. 命名空间错误：XML示例中ConnectionString属性的前缀是DTS，属于www.microsoft.com/SqlServer/Dts命名空间，原脚本错误地使用自定义的DTSConnection命名空间定位该属性，导致无法匹配节点。
2. 多连接场景未处理：原脚本仅尝试提取第一个匹配的连接字符串，未考虑一个SSIS包可能包含多个连接管理器的情况。

修复后的脚本 #

$folderPath = "Z:\Database\SSIS\Test"
$namespace = @{
    DTS = "www.microsoft.com/SqlServer/Dts"
    SQLTask = "www.microsoft.com/sqlserver/dts/tasks/sqltask"
}

# 获取指定文件夹下所有.dtsx文件（包括子目录）
$packageFiles = Get-ChildItem -Path $folderPath -Filter "*.dtsx" -Recurse

foreach ($file in $packageFiles) {
    Write-Output "=== 处理文件: $($file.FullName) ==="

    # 加载XML文件
    $xml = [xml](Get-Content $file.FullName)

    # 提取SQL语句（支持多个SQL任务）
    $sqlStatements = Select-Xml -Xml $xml -Namespace $namespace -XPath "//DTS:ObjectData/SQLTask:SqlTaskData/@SQLTask:SqlStatementSource" | ForEach-Object { $_.Node.Value }
    if ($sqlStatements) {
        Write-Output "SQL语句:"
        $sqlStatements | ForEach-Object { Write-Output "- $_" }
    } else {
        Write-Output "未找到SQL语句"
    }

    # 提取所有连接管理器的名称和对应连接字符串
    $connections = Select-Xml -Xml $xml -Namespace $namespace -XPath "//DTS:ConnectionManager[@DTS:ObjectName]" | ForEach-Object {
        $connManager = $_.Node
        $connStringNode = Select-Xml -Xml $connManager -Namespace $namespace -XPath "./DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString"
        [PSCustomObject]@{
            ConnectionName = $connManager.GetAttribute("DTS:ObjectName")
            ConnectionString = if ($connStringNode) { $connStringNode.Node.Value } else { "无连接字符串" }
        }
    }

    Write-Output "连接信息:"
    $connections | ForEach-Object {
        Write-Output "- 名称: $($_.ConnectionName)"
        Write-Output "  连接字符串: $($_.ConnectionString)"
    }

    Write-Output "`n"
}

脚本优化说明 #

• 移除了多余的DTSConnection命名空间，统一使用DTS命名空间定位所有相关节点和属性。
• 调整SQL语句提取逻辑，支持输出多个SQL任务的语句。
• 新增连接管理器名称提取，处理一个包中多个连接的场景，清晰展示每个连接的名称和对应字符串。
• 增加空值判断，避免无匹配内容时输出空结果。

内容的提问来源于stack exchange，提问作者tomfbsc