使用 PowerShell 从 SSIS 目录中获取包 XML [英] Get Package XML from SSIS Catalog with PowerShell

问题描述

有没有办法使用 PowerShell 从 SSIS 目录中的包中获取包 XML，但无需下载和解压缩项目?我想在 XML 文档中搜索某些字符串.

Is there a way to get the Package XML from the packages that are in the SSIS Catalog with PowerShell, but without downloading and extracting the project? I want to search for certain strings within the XML document.

################################
########## 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)
        }
    }
}

推荐答案

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

Albeit the thread being a few months old, I believe the below approach directly answers the original question. I encountered a similar situation whereby I needed to access the XML from a package in SSISDB to serve as a template for new packages. Well, after reading the project bytes into my script,

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

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

it was possible to bypass downloading/unzipping by creating an in-memory archive from the project bytes. By iterating through the archive entries until the correct package was found (easier if the index of the file is known), the last step was to read the archive entry into a stream and pass it to the package.LoadFromXML method.

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;

    }

}

这篇关于使用 PowerShell 从 SSIS 目录中获取包 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！