Get Package XML from SSIS Catalog with PowerShell

Question

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

Answer 1

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();

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;

    }

}