In diesem Blogbeitrag 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 Blogbeitrag 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:
- SQL Server Management Studio öffnen
- Im SQL Server Objekt-Explorer „Integration Services-Kataloge“ auswählen, Rechtsklick auf „Katalog erstellen…“
Abbildung 1: SSIS-Katalog erstellen
- 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.
Abbildung 2: SSIS-Katalogserstellung Dialogbox
- 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.
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).
- Nach einer Aktualisierung des Objekt-Explorers erscheint die SSISDB-Datenbank unter dem Datenbankenverzeichnis und der SSISDB-Katalog unter dem Integration Services-Katalogverzeichnis.
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.
Abbildung 5: SSIS-Katalogkonfiguration
Beispiel einer Katalogkonfiguration mithilfe der gespeicherten Prozedur aus der SSISDB-Datenbank:
Anzahl der maximalen Projektversionen auf 12 erhöhen
EXEC[SSISDB].[catalog].[configure_catalog]
@property_name=N'MAX_PROJECT_VERSIONS'
,@property_value=N'12'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.
Abbildung 6: SSIS-Katalogordner erstellen
Analog können Katalogordner mit der folgenden Prozedur erstellt werden:
DECLARE @folder_id BIGINT
EXEC [SSISDB].[catalog].[create_folder]
@folder_name=N'Chair_ETL'
,@folder_id=@folder_id OUTPUT
SELECT @folder_id
EXEC [SSISDB].[catalog].[set_folder_description]
@folder_name=N'Chair_ETL'
,@folder_description=N'Daten extrahieren'
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.
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.
- Data Tools öffnen
- Rechtsklick auf das geöffnete Projekt im Projektmappen-Explorer
- Auf Deploy (in manchen Versionen auch „Bereitstellen“) klicken, dann startet der Bereitstellungsassistent
Abbildung 11: SSDT Projektbereitstellung
4) In der Bereitstellungs-Assistent-Dialogbox:
- Quelle auswählen (aus einer Projektbereitstellungsdatei oder SSIS-Katalog)
Abbildung 12: Bereitstellungs-Assistent Dialogbox Quelle auswählen
- Ziel auswählen (SSIS-Servername und Pfad)
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.
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.
Abbildung 15: ISPAC-Datei in SSDT erstellen
Das Ergebnis des Erstellungsprozesses kann aus dem Ausgabefenster abgelesen werden.
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.
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:
USE SSISDB
GO
DECLARE @folder_id BIGINT
EXEC [SSISDB].[catalog].[create_folder]
@folder_name=N'Chair_ETL_Copy',
@folder_id=@folder_id OUTPUT
EXEC [SSISDB].[catalog].[set_folder_description]
@folder_name=N'Chair_ETL_Copy',
@folder_description=N'Eine Kopie für T-SQL Deployment.'
DECLARE @ProjectBinary AS VARBINARY(MAX)
DECLARE @operation_id AS BIGINT
SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'K:\BLOG_SOW\Mate rial\SSIS\SSIS_Catalog_Blog\bin\Development\SSIS_Catalog_Blog.ispac',
SINGLE_BLOB) as BinaryData)
EXEC catalog.deploy_project
@folder_name = 'Chair_ETL_Copy'
,@project_name = 'SSIS_Catalog_Blog'
,@Project_Stream = @ProjectBinary
,@operation_id = @operation_id OUTHinweis: 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.
USE SSISDB
GO
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
@package_name = N'00_MasterPackage.dtsx' -- Paketname
,@execution_id = @execution_id OUTPUT
,@folder_name = N'Chair_ETL' -- Katalogordner
,@project_name = N'SSIS_Catalog_Blog' -- Projektname
,@use32bitruntime = False -- Runtime-Umgebung (64Bit)
,@reference_id=null -- Ohne reference (Umgebungsvariab-le)
EXEC [SSISDB].[catalog].[start_execution] -- die eigentliche Ausführung
@execution_id
GOHinweis: 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.
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.
