Mit dem neuen Projektdeploymentmodell der Integration Services 2012, werden bei einem Deployment Projekte statt einzelner Pakete bereitgestellt. Diese landen dann auch nicht mehr wie beim Paketdeploymentmodell in der MSDB, sondern die SSIS Projekte (und damit auch deren Pakete) sind über eine separate Datenbank, dem SSIS Catalog (oder auch SSIDB genannt), erreichbar. Von dort können mittels gespeicherter Prozeduren neue Paketausführungen gestartet werden, was aber wenn man ein einzelnes Paket aus der SSISDB extrahieren möchte, beispielsweise um dieses zu analysieren oder separat zu speichern?

Ähnlich wie beim Paketdeploymentmodell lässt sich diese Aufgabe „zu Fuß“ recht einfach lösen: entweder man erstellt ein neues SSIS Projekt in den SQL Server Data Tools (SSDT) und importiert ein im SSIS Catalog deploytes Projekt oder man exportiert das Projekt direkt aus dem SSIS Catalog in eine .ispac Datei die sich wiederum als ZIP entpacken lässt und einen Zugriff auf die einzelnen Pakete ermöglicht. Wie das genau funktioniert wird z.B. hier detailliert beschrieben: http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html

In unserem Szenario gilt es allerdings den Export zu automatisieren, Ziel soll es sein den Zugriff auf ein einzelnes Paket zu haben um dieses zu speichern oder über das SSIS Objektmodell weiter zu analysieren. Bei einem intensiveren Blick auf die Tabellen der SSISDB fällt hierbei die Tabelle [internal].[packages] auf, die offenbar für jedes in Projekten deployte Paket einen Eintrag enthält. Hier existiert sogar eine Spalte [package_data] vom Typ varbinary(max) in der man das eigentliche Paket vermuten könnte. Diese Spalte ist aber stets NULL, womit die Tabelle für dieses Szenario ausscheidet.

Nach weiterer Suche durch die teils spärlich dokumentierten Strukturen der SSISDB gelangt man schließlich zur Tabelle [internal].[object_versions] in der sich zu allen deployten Projektversionen ein BLOB befindet (Spalte [object_data]). Dies ist der Einstiegspunkt um auch programmatisch an ein Projekt und damit auch dessen Pakete zu gelangen.

Ein möglicher Ansatz zur Projektextraktion sieht nun wie folgt aus:

  • Extraktion der relevanten [project_id] aus der Tabelle [internal].[projects]
  • Setzen eines Encryption Keys = "MS_Enckey_Proj_" + [project_id]
  • Setzen eines Certificates = "MS_Cert_Proj_" + [project_id]
  • Generierung eines Schlüssels mit Hilfe des Statements OPEN SYMETRIC KEY für den Encryption Key mit DECRIPTION BY CERTIFICATE und dem Certificate (dieser Schritt ist notwendig da die Projektdaten zunächst verschlüsselt sind)
  • Den generierten Schlüssel holt man sich anschließend aus [internal].[catalog_encryption_keys] mittels DECRYPTBYKEY
  • Nun kann man das Projekt-BLOB entschlüsseln, dazu nutzt man die Skalarfunktion [internal].[decrypt_lob_data] welcher man die Schlüssel sowie die verschlüsselten Projektdaten aus der Tabelle [internal].[object_versions] übergibt
  • Schließlich sollte man noch per CLOSE SYMMETRIC KEY ein wenig „aufräumen“

Das erhaltene Projekt-BLOB könnte man nun abspeichern und die enthaltenen Pakete extrahieren (Behandlung der .ispac Datei als ZIP). Wir verfolgen aber auch hier den programmatischen Ansatz (in diesem Fall über C#) – der folgende Codeschnipsel ließe sich entsprechend in ein SSIS Paket integrieren um den gesamten Prozess zu automatisieren:

System.IO.MemoryStream memStream = new System.IO.MemoryStream(data);
System.IO.Packaging.Package zipFile = System.IO.Packaging.ZipPackage.Open(memStream);
foreach (System.IO.Packaging.PackagePart part in zipFile.GetParts())
{
  if (part.Uri.OriginalString.EndsWith(PackageName))
  {
    System.IO.StreamReader reader = new System.IO.StreamReader(part.GetStream());
    string xml = reader.ReadToEnd();
    reader.Close();
    package.LoadFromXML(xml, null);
  }
}
zipFile.Close();

In der Variable package befindet sich nun das gewünschte SSIS Paket, womit eine Weiterverarbeitung über das SSIS Objektmodell möglich wäre. Weitere Informationen dazu gibt es im Integration Services Developer Guide: http://technet.microsoft.com/en-us/library/ms136025%28v=sql.110%29.aspx

Category: Blog
Schlagwörter: