xml - 使用 PowerShell 从 SSIS 目录获取包 XML

Question

有没有一种方法可以使用 PowerShell 从 SSIS 目录中的包中获取包 XML，但无需下载和解压缩项目？我想在 XML 文档中搜索某些字符串。

################################
########## PARAMETERS ##########
################################ 
$SsisServer = ".\sql2016"



############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
    Throw [System.Exception] "Failed to connect to server $SsisServer "
}



#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    Throw [System.Exception] "SSISDB catalog doesn't exist!"
}



##########################
########## LOOP ##########
##########################
foreach ($Folder in $Catalog.Folders)
{
    Write-Host $Folder.Name
    foreach ($Project in $Folder.Projects)
    {
        Write-Host " - " $Project.Name
        foreach ($Package in $Project.Packages)
        {
            Write-Host "    - " $Package.Name
            # HOW TO GET PACKAGE XML???

            # Not working:
            # Exception calling "Serialize" with "1" argument(s):
            # "The parameter 'sink.Action' is invalid."
            #$sb = New-Object System.Text.StringBuilder
            #$sw = New-Object System.IO.StringWriter($sb)
            #$writer = New-Object System.Xml.XmlTextWriter($sw)
            #$xml = $Package.Serialize($writer)
        }
    }
}

Answer 1

尽管该线程已有几个月历史，但我相信以下方法直接回答了原始问题。我遇到了类似的情况，我需要从 SSISDB 中的包访问 XML 以用作新包的模板。好吧，在将项目字节读入我的脚本之后，

var projectBytes = ssisServer.Catalogs["SSISDB"].Folders[SSISFolderName].Projects[SSISProjectName].GetProjectBytes();

可以通过从项目字节创建内存存档来绕过下载/解压缩。通过迭代存档条目直到找到正确的包(如果文件索引已知会更容易)，最后一步是将存档条目读入流并将其传递给 package.LoadFromXML 方法。

Stream stream = new MemoryStream(projectBytes);

ZipArchive za = new ZipArchive(stream);

foreach (ZipArchiveEntry zipEntry in za.Entries)
{
    if (zipEntry.FullName == SSISPackageName)
    {
         Package pkg = new Package();

         StreamReader sr = new StreamReader(zipEntry.Open());

         pkg.LoadFromXML(sr.ReadToEnd(), null);

         break;

    }

}