Suchen...
Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

SSIS-Katalog Teil 1

In diesem Beitrag geht es um eine besondere Funktionalität der SQL Server Integration Services, welche zum ersten Mal in der Version 2012 vorgestellt wurde. Der Integration Services-Katalog (SSIS–Katalog) bietet die Möglichkeit, SSIS-Projekte extern zu entwickeln und ganz einfach in dem SSISDB-Katalog bereitzustellen. Von dort aus können die Objekte (Projekte, Pakete, Parameter und Umgebungen) und die Ausführungsprozesse mit geringem Aufwand und ganz bequem über die GUI oder mit Hilfe der SSISDB-Datenbank-Sichten und -Prozeduren gesteuert, analysiert, überwacht und gewartet werden.

Der Beitrag zeigt schrittweise, wie
– ein SSIS-Katalog erstellt wird
– ein SSIS-Projekt im SSIS-Katalog bereitstellt wird
– ein SSIS-Paket aus dem SSIS-Katalog ausgeführt wird

und macht deutlich, wie mit dieser neuen SQL Server Funktionalität ein nachhaltiger, professioneller und standardisierter ETL-Prozess geschaffen werden kann.

SSIS-Katalog erstellen

Zur Erstellung eines SSIS-Katalogs sind die folgenden Schritte notwendig:

  1. SQL Server Management Studio öffnen
  2. Im SQL Server Objekt-Explorer „Integration Services-Kataloge“ auswählen, Rechtsklick auf „Katalog erstellen…“
    SSIS_Katalog_erstellen

      Abbildung 1 SSIS-Katalog erstellen

  3. In der Dialogbox:
    • CLR-Integration muss aktiviert werden
    • „Automatische Ausführung …“ nicht aktivieren, sonst eine Prozedur zur Bereinigung alle Operationen bei jedem SQL Server Start
    • Der Name der Katalogdatenbank kann nicht geändert werden (die SSISDB existiert bereits in der Installation von SQL Server und wird bei der SSIS-Katalogerstellung wiederhergestellt )
    • Es muss zwingend ein Kennwort zur Verschlüsselung der SSIS-Katalogdaten festgelegt werden.SSIS_Katalogserstellung_Dialogbox

      Abbildung 2 SSIS-Katalogserstellung Dialogbox

  4. Auf OK klicken
    Achtung:

    • Es kann nur ein SSIS-Katalog pro SQL Server Installation erstellt werden
    • Wird die Komponente Integration Services bei der Installation von SQL Server nicht mit installiert, so kann der SSIS-Katalog nicht erstellt werden und man bekommt eine Fehlermeldung wie in der Abbildung unten.Fehler_bei_der_Katalogerstellung

      Abbildung 3 Fehler bei der Katalogerstellung

    • Der Error-Message bedeutet: Es fehlt die bereits erwähnte SSISDBBackup.bak -Datei (eine Kopie aus einer anderen SQL Server Installation ist nicht zu empfehlen).
  5. Nach einer Aktualisierung des Objekt-Explorers erscheint die SSISDB-Datenbank unter dem Datenbankenverzeichnis und der SSISDB-Katalog unter dem Integration Services-Katalogverzeichnis.

    Übersicht_SSIDB_und_SSIDB_Katalog

    Abbildung 4 Übersicht SSIDB und SSIDB-Katalog

Konfiguration des SSIS-Katalogs

Das Verhalten des SSISDB-Katalogs wird durch dessen Konfiguration bestimmt. Diese kann entweder per Rechtsklick auf SSISDB im Objekt-Explorer unter Integration Services-Kataloge oder mit einer Abfrage der View catalog.catalog_properties (SSISDB-Datenbank) angezeigt werden.
Der SSIS-Katalog und die zugehörige Datenbank werden mit Default-Werten erstellt. Es wird empfohlen die Konfiguration nach eigenen Bedürfnissen anzupassen. Diese Anpassung kann teilweise direkt im Dialogfeld des SSISDB-Katalogs oder über die gespeicherte Prozedur catalog.configure_catalog aus der SSISDB-Datenbank erfolgen.

SSIS_Katalogkonfiguration

Abbildung 5 SSIS-Katalogkonfiguration

Beispiel einer Katalogkonfiguration mithilfe der gespeicherten Prozedur aus der SSISDB-Datenbank:
Anzahl der maximalen Projektversionen auf 12 erhöhen

Objekte des SSIS-Katalogs:

Der SSIS-Katalog besteht aus Projekten, Paketen und Umgebungen, die in einer Ordnerstruktur orga-nisiert sind. Zu diesen Objekten gibt es Umgebungseinstellungen, Projekt- und Paket-Parameter, die in verschiedenen SSIS-Konfigurationen verwendet werden können.
Katalogordner: ist der Behälter für die Projekte, Pakete und Umgebungen. Auf dieser Ebene können Berechtigungen für die SSIS-Katalogobjekte angewendet werden. Beim Erstellen eines Ordners werden automatisch ein Projektordner und ein Umgebungsordner mit erstellt. Mit einem Rechtsklick auf den SSISDB-Katalog oder über die Prozeduren create_folder und set_folder_description der SSISDB-Datenbank kann ein Katalogordner erstellt werden.

SSIS_Katalogordner_erstellen

Abbildung 6 SSIS-Katalogordner erstellen

Analog können Katalogordner mit der folgenden Prozedur erstellt werden:

SSIS_Katalog_Ordnerstruktur

Abbildung 7 SSIS-Katalog Ordnerstruktur

Projekte und Pakete: ein SSIS-Projekt kann ein oder mehrere Pakete enthalten, diese Projekte und Pakete können wiederum Parameter und Umgebungsverweise enthalten. Mit Hilfe der in der SSISDB enthaltenen Prozeduren können die SSIS-Projekte gesteuert werden (z. B. bereitstellen, verschieben, wiederherstellen oder löschen):

  • catalog.deploy_project
  • catalog.move_project
  • catalog.restore_project
  • catalog.delete_project

Anhand der folgenden Views können die Details zu den Paketen, Projekten und Projektversionen angezeigt werden:

  • catalog.projects
  • catalog.packages
  • catalog.object_versions

Umgebung und Parameter: die Projekt- und Paketparameter sind direkt mit der Umgebung verbunden und können dort gesteuert werden. Dieser Mechanismus ermöglicht eine einfa-che und dynamische Konfiguration der Projekte und Pakete zur Laufzeit. Mit den folgenden Prozeduren können Umgebungen erstellt, verschoben oder gelöscht werden.

  • catalog.create_environment
  • catalog.move_environment
  • catalog.delete_environment

Mit den folgenden Views können die Projekt- und Paketparameter sowie die Parameterwerte zur Laufzeit angezeigt werden:

  • catalog.object_parameters
  • catalog.execution_parameter_values

SSIS-Katalog bereitstellen (Deployment)

Der Begriff SSIS-Projekt-Deployment in SSIS-Katalog bezeichnet der Übertragungsprozess eines SSIS-Projekts aus der Entwicklungsumgebung in die Produktivumgebung. In SQL Server Integration Services gibt es verschiedene Möglichkeiten ein Projekt im SSIS-Katalog bereitzustellen: in SQL Server Data Tools (SSDT) mit dem Deployment Wizard, über eine generierte ISPAC-Datei oder mit T-SQL in Verbindung mit einer ISPAC-Datei.
Als Beispiel soll ein einfacher Datenimport dienen, der Prozess kann aber auf anderen Szenarien über-tragen werden. In dem Beispiel wird das Konzept des „Master Package“ benutzt. Dies ist ein SSIS-Ansatz zur Entwicklung, Bereitstellung, Wartung und dem Betrieb von SSIS-Paketen. Das Konzept wird verwendet um eine zentrale Ablaufsteuerung und eine gemeinsame und zentrale Konfiguration aller einzelnen Pakete zu ermöglichen. An dieser Stelle wird das Master Package Konzept lediglich zur Ausführung der Child-Packages benutzt.

Customer_dtsx_Paket    Sales_dtsx_Paket    Master_dtsx_Paket_Bereitstellungsoption1_SSDT_Deployment_Wizard

Abbildung 8 Customer dtsx-Paket; Abbildung 9 Sales dtsx-Paket; Abbildung 10 Master dtsx-PaketBereitstellungsoption 1: SSDT + Deployment Wizard

Um das SSIS-Projekt oder die einzelnen Pakete im SSISDB-Katalog zu deployen sind folgende Schritte notwendig.

  1. Data Tools öffnen
  2. Rechtsklick auf das geöffnete Projekt im Projektmappen-Explorer
  3. Auf Deploy (in manchen Versionen auch „Bereitstellen“) klicken, dann startet der Bereitstellungsassistent

SSDT_Projektbereitstellung

Abbildung 11 SSDT Projektbereitstellung

4) In der Bereitstellungs-Assistent-Dialogbox:

  • Quelle auswählen (aus einer Projektbereitstellungsdatei oder SSIS-Katalog)

Bereitstellunsassistent_Dialogbox_Quelle_auswählen

Abbildung 12 Bereitstellungs-Assistent Dialogbox Quelle auswählen

  • Ziel auswählen (SSIS-Servername und Pfad)

Bereitstellunsassistent_Dialogbox_Ziel_auswählen

Abbildung 13 Bereitstellungs-Assistent Dialogbox Ziel auswählen

  • Überprüfen der Angaben und anschließend bereitstellen
  • Ergebnis überprüfen

Wenn alles fehlerfrei läuft, wird das Projekt inklusive aller Pakete in den SSIS-Katalog geladen und gespeichert.

Katalogübersicht_nach_der_Projektbereitstellung

Abbildung 14 Katalogübersicht nach der Projektbereitstellung

Analog zu diesem Vorgehen kann man im Data Tools auch ein einzelnes Paket bereitstellen, hierzu klickt man einfach mit der rechten Maustaste auf das Paket im Projektmappen-Explorer und danach auf Deploy. Dann startet der Bereitstellungs-Assistent.
Hinweis: es muss kein Katalogordner vorhanden sein, dieser kann auch im Bereitstellungs-Assistenten beim Projekt- oder Paketbereitstellungsprozess erstellt werden.

Bereitstellungsoption 2: ISPAC-Datei + Deployment Wizard

Manchmal dürfen ETL-Entwickler keine Pakete auf einen SQL Integration Services Server bereitstellen, sondern müssen Dateien an einen Administrator zur Bereitstellung übergeben. Mit Hilfe der Integration Services kann eine Projektbereitstellungsdatei mit dem Format .ISPAC, welche alle Metadaten der Projekt- und Paketparameter enthält, erstellt werden. Diese ISPAC-Datei können entweder in SSDT oder im SSMS erstellt werden.
In SSDT: Rechtsklick auf das Projekt im Projektmappen-Explorer und anschließend Erstellen wählen. So wird die Datei erstellt und im SSIS-Verzeichnis unter \bin\Deployment\datei.ispac abgelegt.

ISPAC_Datei_in_SSDT_erstellen

Abbildung 15 ISPAC-Datei in SSDT erstellen

Das Ergebnis des Erstellungsprozesses kann aus dem Ausgabefenster abgelesen werden.

Ausgabefenster Erstellung ISPAC-Datei

Abbildung 16 Ausgabefenster Erstellung ISPAC-Datei

Im SSMS: Projekt aus dem SSISDB-Katalog mit der rechten Maustaste auswählen und auf Exportieren klicken, danach den Dateinamen eingeben und speichern. Diese Methode, eine ISPAC-Datei zu erstellen, kann im Rahmen einer SQL-Servermigration sehr praktisch sein.

ISPAC-Datei erstellen im Management Studio

Abbildung 17 ISPAC-Datei erstellen im Management Studio

Mit einem Doppelklick auf die ISPAC-Datei wird der Bereitstellungs-Assistent gestartet, falls nicht, dann muss man die Datei mit einem rechten Mausklick auswählen und Öffnen mit … klicken. Danach die Anwendung „ISDeploymentWizard“ auswählen und die Schritte in der Dialogbox ausführen.

Bereitstellungsoption 3: T-SQL + ISPAC-Datei

Mit Hilfe der in der SSISDB gespeicherten Prozeduren können SSIS-Projekte mit SQL-Abfragen im SSIS-Katalog bereitgestellt werden. Hierzu kann die Open Row Set-Funktion benutzt werden, um die ISPAC-Datei aus einer binären Variable zu lesen und dann die Prozedur Catalog.Deploy_Project aus-zuführen, um das Projekt in einen Zielordner und ein Projekt bereitzustellen. Die zugehörige SQL-Abfrage sieht wie folgt aus:

Hinweis: nach einer Paket- oder Projektänderung in der Entwicklungsumgebung kann das Projekt erneut bereitgestellt werden. Integration Services überschreibt das bereits im Katalogordner vorhandene Projekt nicht, stattdessen wird eine neue Version des Projektes erstellt und als aktuelle Version gesetzt. So hat man die Möglichkeit zwischen den Versionen zu wechseln. Per Default ist die Anzahl der Versionen in der Katalogkonfiguration auf 10 festgelegt.

SSIS-Katalog Pakete ausführen

Im SQL Server Integration Services gibt es verschiedene Möglichkeiten die im SSISDB-Katalog bereit-gestellten Pakete auszuführen. Man kann das Paket über die GUI im Management Studio selektieren und manuell ausführen, mit einem SQL-Statement in Kombination mit den in der SSISDB gespeicher-ten Prozeduren starten oder die Ausführung ganz mit dem SQL Server-Agent automatisieren. Dar-über hinaus gibt es die Möglichkeit, die DTExec Utility zu verwenden.

Paketausführung mit SQL-Abfragen

In der SQL-Abfrage wird zuerst eine eindeutige Ausführungsidentifikationsnummer (Execution_ID) mit der Prozedur [catalog].[create_execution] erstellt und in einer Variable gespeichert, danach wird diese Variable in der Prozedur [catalog].[start_execution] verwendet um das Paket auszuführen.

Hinweis: Es besteht die Möglichkeit Parameter und Umgebungsvariablen in der SQL-Abfrage einzugeben. Hierzu werden für den Parameterwert die „Object Type Codes“ des SSISDB-Katalogs verwen-det (@object_type=20 ⇒ Projektparameter, 30 ⇒ Paketparameter und 50 ⇒ Logging-Parameter).

Paketausführung mit der DTExec Utility

SSIS-Pakete können auch mit Hilfe der DTExec Utility in der Windows-Eingabeaufforderung ausge-führt werden. Dafür kann ein DTExec-Skript geschrieben und als .bat gespeichert werden. Das Skript sieht dann folgendermaßen aus.


In das Argument /ISSERVER wird der Paketname mit dem Pfad zum Katalogordner eingetragen, in /SERVER wird die SQL-Server Instanz eingetragen und der Parameter /Par $ServerOpti-on::SYNCHRONIZED wird auf True gesetzt, um den Ausführungsprozess direkt in der Eingabeaufforderung anzuzeigen.

Paketausführung über den SQL Server-Agent

Für die Paketausführung über den SQL Server-Agent wird ein Job benötigt, der einen Schritt vom Typ T-SQL enthält, in dem das SQL-Statement zur Paketausführung in das Befehlseingabefeld wie in un-tenstehender Abbildung eingefügt wird.

Paketausführung mit SQL Server-Agent

Abbildung 18 Paketausführung mit SQL Server-Agent

Fazit

Der Integration Services SSIS-Katalog bietet mit relativ geringem Aufwand die Möglichkeit, SSIS-Projekte extern zu entwickeln und diese in einer produktiven Umgebung im SSISDB-Katalog bereitzu-stellen und diese Objekte mit Hilfe der zugrundeliegenden SSISDB-Datenbank zu verwalten, zu über-wachen und zu warten. Des Weiteren können mit Hilfe der integrierten Logging-Funktion die Ausfüh-rungen genau überwacht und analysiert werden (Thema SSIS-Katalog Teil 2). Mit dieser neuen SQL Server Funktionalität kann ein nachhaltiger, professioneller und standardisierter ETL-Prozess geschaffen werden.