Obtenir le package XML du catalogue SSIS avec PowerShell

Question

Existe-t-il un moyen d'obtenir le Package XML à partir des packages qui se trouvent dans le Catalogue SSIS avec PowerShell, mais sans télécharger et extraire le projet ? Je veux rechercher certaines chaînes dans le document XML.

################################
########## PARAMÈTRES ##########
################################ 
$SsisServer = ".\sql2016"

############################
########## SERVEUR ##########
############################
# Charger l'assembly Integration Services
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Créer une connexion au serveur
$SqlConnectionstring = "Source de données=" + $SsisServer + ";Catalogue initial=master;Sécurité intégrée=SSPI;"
$SqlConnection = Nouvel objet Système.Data.SqlClient.SqlConnection $SqlConnectionstring

# Créer l'objet Integration Services
$IntegrationServices = Nouvel objet $SsisNamespace".IntegrationServices" $SqlConnection

# Vérifier si la connexion a réussi
if (-not $IntegrationServices)
{
    Throw [System.Exception] "Échec de la connexion au serveur $SsisServer "
}

#############################
########## CATALOGUE ##########
#############################
# Créer un objet pour le Catalogue SSISDB
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Vérifier si le Catalogue SSISDB existe
if (-not $Catalog)
{
    Throw [System.Exception] "Le catalogue SSISDB n'existe pas!"
}

##########################
########## BOUCLE ##########
##########################
foreach ($Folder dans $Catalog.Folders)
{
    Write-Host $Folder.Name
    foreach ($Project dans $Folder.Projects)
    {
        Write-Host " - " $Project.Name
        foreach ($Package dans $Project.Packages)
        {
            Write-Host "    - " $Package.Name
            # COMMENT OBTENIR LE PACKAGE XML ???

            # Non fonctionnel :
            # Exception lors de l'appel de "Sérialiser" avec "1" argument(s) :
            # "Le paramètre 'sink.Action' n'est pas valide."
            #$sb = Nouvel objet Système.Text.StringBuilder
            #$sw = Nouvel objet Système.IO.StringWriter($sb)
            #$writer = Nouvel objet Système.Xml.XmlTextWriter($sw)
            #$xml = $Package.Serialize($writer)
        }
    }
}

Answer 1

Je n'ai pas eu de chance en essayant une version purement basée sur le modèle d'objet de ce code car j'obtenais une erreur

Exception lors de l'appel à "Serialize" avec "1" argument(s) : "Le paramètre 'sink.Action' est invalide".

Génial ! Après avoir galéré pendant une heure en essayant d'accéder au projet via le Managed Object Model, j'ai abandonné et suivi la route TSQL. Nous avons simplement besoin d'imiter l'appel à SSISDB.catalog.get_project et de lui passer les noms du dossier et du projet

Function Get-ProjectsTsql
{
    param
    (
        [Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder] $folder
    ,   [string] $serverName = "localhost\dev2016"
    )

    # nous voulons construire un appel de procédure d'exécution qui ressemble à ceci
    # exec [SSISDB].[catalog].[get_project] @folder_name=N'FolderName',@project_name=N'ProjectName'

    $connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)
    # Le seul et unique catalogue SSISDB
    $catalog = $integrationServices.Catalogs["SSISDB"]

    # Instance de ProjectInfo
    foreach ($proj in $folder.Projects)
    {
        $projName = $proj.Name
        $folderName = $folder.Name

        $zipOut = "C:\tmp\$projName.zip"

        $query = "exec [SSISDB].[catalog].[get_project] @folder_name=N'$folderName',@project_name=N'$projName'"
        # Write-Host $query

        $command = New-Object System.Data.SqlClient.SqlCommand
        $command.CommandText = $query
        $command.Connection = $connection
        $projectBinary = $command.ExecuteScalar()
        [System.IO.File]::WriteAllBytes($zipOut, $projectBinary)
    }
}

Function Get-CatalogFolders
{
    param
    (
        [string] $serverName = "localhost\dev2012"
    )

    $connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)

    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

    $integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)
    # Le seul et unique catalogue SSISDB
    $catalog = $integrationServices.Catalogs["SSISDB"]

    $catalogFolders = $catalog.Folders

    return $catalogFolders
}

$serverName = "localhost\dev2016"
# Identifier tous les dossiers sur le serveur
foreach ($folder in Get-CatalogFolders $serverName)
{
    # Sauvegarder tous les projets dans leurs fichiers zip
    Get-ProjectsTsql $folder $serverName
}

Cela récupère l'ispac que je sauvegarde avec une extension .zip (car ce n'est qu'un zip). Pour obtenir le xml du package, vous devez alors dézipper le fichier, trouver votre package et continuer à partir de là

Answer 2

Bien que le fil de discussion soit vieux de quelques mois, je crois que l'approche ci-dessous répond directement à la question originale. J'ai rencontré une situation similaire où j'avais besoin d'accéder au XML d'un package dans SSISDB pour servir de modèle pour de nouveaux packages. Eh bien, après avoir lu les octets du projet dans mon script,

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

il était possible de contourner le téléchargement/décompression en créant une archive en mémoire à partir des octets du projet. En parcourant les entrées de l'archive jusqu'à ce que le package correct soit trouvé (plus facile si l'index du fichier est connu), la dernière étape était de lire l'entrée de l'archive dans un flux et de la passer à la méthode 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;

    }

}

Answer 3

Si l'objectif est de lire les données dans le package et d'obtenir directement le XML, il semble que le code XML des packages est en fait stocké sous forme de données binaires dans la colonne object_data de la table SSISDB.internal.object_versions.ref.[1]

Vous pouvez utiliser la méthode GetSqlBinary pour récupérer ces données. J'ai vu un exemple d'utilisation de cela pour récupérer des fichiers rdl SSRS. Voir la réf. [2] pour un exemple complet du code.

    # Nouveau BinaryWriter; le fichier existant sera écrasé. 
    $fs = New-Object System.IO.FileStream ($name), Create, Write; 
    $bw = New-Object System.IO.BinaryWriter($fs); 

    # Lecteur complet du Blob avec GetSqlBinary 
    $bt = $rd.GetSqlBinary(2).Value; 
    $bw.Write($bt, 0, $bt.Length); 
    $bw.Flush(); 
    $bw.Close(); 
    $fs.Close(); 

Références

1. Message du forum MSDN

2. Exporter tous les rapports SSRS, sources de données et ressources