In vielen Projekten wird ein SQL-Agent-Job definiert, der ein dtsx-Paket auf Verzeichnisebene aufruft. Hierbei findet die Verwaltung des dtsx-Pakets und des zugehörigen Projektes innerhalb des Verzeichnissystems statt. Seit dem SQL Server 2012 gibt es ein Werkzeug, um SSIS-Pakete zu verwalten und Fehleranalysen durchzuführen. Im folgenden Blogbeitrag werden die Arbeitsweise und nützliche Funktionen dieses Tools vorgestellt. Des Weiteren gehen wir auf die SSISDB ein und erläutern wie man Steuermechanismen erstellt, um SSIS-Pakete aus der Datenbank zu starten bzw. auf Fehlersuche zu gehen.

Integration Services Katalog im SQL-Server-Management-Studio

Wo finde ich den Katalog?

Die Administration des „Integration Service Katalog“ (fortfolgend nur „Katalog“ genannt) kann man über das SSMS (SQL Server Management Studio) erreichen. Innerhalb des Objekt-Explorers gibt es einen Ordner „Integration Service Katalog“, welcher zur Verwaltung und Administration der SSIS-Pakete dient.

Bei einer SQL-Server-Installation wird der SSIS-Katalog nicht automatisch angelegt, sondern muss bei einer ersten Initialisierung noch angelegt/installiert werden.

Falls dies bereits geschehen ist, findet man hier die in dem Katalog veröffentlichten Pakete. Dies bedeutet, der Katalog verwaltet zuvor erstellte Pakete und steuert deren Ausführung.

Er liefert jedoch keine Funktionen, um die Pakete abzuändern. Dies muss im Visual Studio geschehen.

Innerhalb des Katalogs ist es ausschließlich möglich, bestimmte Einstellungen mit Werten zu überschreiben, soweit das SSIS-Paket hierfür mit Projektparametern versehen wurde. Außerdem bietet sich die Möglichkeit bei Datenverbindungen im Paket die Einstellungen zu verändern. Dies kann in Verbindung mit der Technologie der „Environments“ für Test/Produktions-Entwicklungen sehr hilfreich sein.

Wie man hierbei am besten vorgeht und welche Möglichkeiten der Katalog bietet, wird in den folgenden Seiten dargestellt.

Die Objekte des Katalogs und auch die Protokolle werden hierzu innerhalb einer eigenen Datenbank verwaltet (SSISDB). In dieser kann man dann mit SQL-Abfragen nach Ausführungsfehlern von SSIS-Paketen suchen bzw. Prozesse aus dem SQL-Code heraus starten.

Installation des „Integration Services Katalog“

Dass der „Integration Service Katalog“ innerhalb des SQL Servers noch nicht erstellt wurde, sieht man daran, dass keine Verzweigungen unterhalb des Baumknotens „Integration Services Katalog“ auftauchen.

In diesem Fall muss per Rechtsklick im Kontextmenü ein Katalog erzeugt werden.

Es ist nicht möglich mehrere Kataloge innerhalb eines SQL Servers zu haben. Sobald ein Katalog erzeugt wurde ist die Auswahl „Katalog erstellen…“ deaktiviert.

Wichtig:
Zur Erstellung des Katalogs muss die CLR-Integration auf der Instanz aktiviert sein.

SSISDB

Nach einer erfolgreichen Installation findet man im SQL Server Management Studio innerhalb des „Objekt Explorer“ eine Verzeichnisstruktur „Integration Services-Katalog“.

Diese lässt sich aufklappen und gliedert sich in Projekte, jedes Projekt besteht wiederum aus Paketen. Die Pakete sind dtsx-Dateien.

Man kann die Dateien importieren oder direkt aus dem Visual Studio „veröffentlichen“. Das Veröffentlichen eines Paketes oder mehrerer Pakete innerhalb einer Visual Studio Solution wird im weiteren Verlauf gezeigt.

Die SSISDB dient als Verwaltungs-Speicher des „Integration Services-Katalog“. Die Pakete können hier nicht mehr editiert werden. Es ist wichtig die Solution-Dateien auf der Festplatte oder in einem TFS zu verwalten, denn nur diese bieten die Möglichkeit, innerhalb des Visual Studio Anpassungen durchzuführen.

Wie bekomme ich Pakete in den „Katalog“?

Zum einen wäre es möglich die Pakete in den Katalog zu importieren und dann manuell für eine entsprechende Struktur im Katalog zu sorgen. Daneben gibt es die weitere Möglichkeit die Projekte direkt aus dem Visual Studio in den Katalog zu deployen.

Um die ideale Nutzung des Katalogs, die Konfiguration und Fehleranalyse zu ermöglichen, empfiehlt es sich, die Pakete im Visual Studio auf eine bestimmte Art zu bauen. Zur besseren Darstellung kreieren wir im Folgenden ein SSIS-Projekt und stellen daran die Funktionsweise des „Integration Services-Katalog“ dar.

Wir erzeugen zunächst eine Integration Service Projektmappe mit dem Namen „SSIS Katalog Test“. Als Anfang hat das Projekt ein Paket „Test1.dtsx“.

Parameter / Projekt-Connections

Alle Zugriffe auf Quell- oder Zielverbindung innerhalb der Solution stehen später im „Integration Services-Katalog“ zur Editierung zur Verfügung. Alle sonstigen Variablen müssen als Parameter definiert werden.

Variablen sind bereits als „Best practice“ bekannt, um Schreibweisen zu vereinheitlichen und Einstellungen nur an einem zentralen Ort zu pflegen. Es gibt in SSIS-Projekten etwas Ähnliches wie Variablen, die sogenannten Parameter.

Parameter werden einmalig für ein Projekt definiert, wobei Variablen immer für jede einzelne dtsx-Datei definiert werden müssen.

Wichtig:
Innerhalb des „Integration Services-Katalog“ sind nur die Parameter und Einstellungen für Datenverbindungen sichtbar und können überschrieben werden. Im SSIS-Paket verwendete Variablen sind im „Integration Services-Katalog“ nicht mehr sichtbar!

Wir werden nun den Servernamen und den Datenbanknamen für einen späteren OLEDB Connection-String erzeugen. Das Vorgehen ist hierbei identisch mit dem Erzeugen einer Variablen innerhalb eines dtsx-Pakets.

Im Screenshot kann man allerdings anhand der Spalten bei der Parametererstellung bereits sehen, dass es gewisse Unterschiede gibt. Die Spalten „Vertraulich“ und „Erforderlich“ sind bei Variablen nicht verfügbar.

Dann werden die Parameter verwendet, um die OLEDB-Connection zu editieren. Anhand der Benamung „@[$Project::   ]“ erkennt man auch hier einen Unterschied zu den Variablen innerhalb von SSIS-Paketen.

Beispiel-Anwendung

Um später die Verwendung der hier theoretisch beschriebenen Vorgehensweise zu demonstrieren, erstellen wir eine Beispiel-Anwendung.

Hierzu definieren wir ein Paket, welches aus drei einfachen Schritten besteht. Alle drei Schritte sind in diesem Fall „SQL Tasks“.

• Schritte 1 und 3 mit der simplen Abfrage „SELECT 1“.
• In Schritt 2 wird ein Fehler simuliert mit der Abfrage: „SELECT 1/0“

Hierdurch soll gezeigt werden, wie sich später im „Katalog“ nach Fehlern suchen lässt.

Veröffentlichen (Deploy)

Wenn das Paket fertig gestellt ist, dann muss dieses in den „Integration Service Catalog“ geladen werden. Hierzu gibt es die Funktion „Veröffentlichen“ im Visual Studio.

Im Folgenden werden die einzelnen Einstellungsschritte erläutert:

1. Wählen der Bereitstellen-Funktion innerhalb des Kontextmenü „Solution Explorer“ – „Project“

2. Dann auf “Next” klicken

3. Dann die Servereinstellung wählen und auf „Verbinden“ klicken …

… den Pfad auswählen,

… danach auf „next“ klicken.

4. Und dann „Bereitstellen“

Wie greife ich auf die Pakete im Katalog zu und welche Einstellungen kann ich vornehmen?

Innerhalb des SSMS gibt es nun im Katalog einen Projekteintrag „SSIS Katalog Test“ (Dies ist der Name des Projektes in der Visual Studio Solution). Darunter findet man das Paket „Test1.dtsx“ (Der Name des Pakets aus der Solution).

Über das Kontextmenü – „Konfigurieren“ kann man z. B. das Paket editieren.

Wenn man unter „Bereich“ das Projekt auswählt sieht man die vorher im Visual Studio definierten Parameter.

Wenn man im „Bereich“ die Paketebene auswählt, dann sind die Parameter nicht sichtbar, da diese nur im Projektbereich der Visual Studio Solution definiert wurden.

Genau entgegengesetzt verhält es sich mit den „Verbindungs-Managern“. Da die OLEDB-Verbindung auf Paketebene definiert wurde, ist diese hier auch nur auf Paketebene sichtbar. Man hätte innerhalb des Projekts die OLEDB-Verbindung auch zu einer Projektverbindung machen können, dann wäre diese auf der Projektebene im Katalog sichtbar.

Umgebungen

Innerhalb des Katalogs gibt es die Möglichkeit Umgebungen zu definieren. Eine Umgebung zeichnet sich dadurch aus, dass sie eine Reihe von Variablen definiert. Sie stellt also eine Art „Variablengruppe“ dar. Das kann man nutzen, wenn es zwei Gruppen gibt, welche die gleichen Variablen haben, mit gleichen Namen aber unterschiedlichen Werten (z. B. zwischen Test- und Produktivsystem).

Eine Umgebung wird zuerst erstellt und mit einem Namen und einer Beschreibung versehen.

Anschließend sieht man die Umgebung im Katalog und kann diese über das Kontextmenü mit „Eigenschaften“ editieren.

Wir definieren zwei neue Variablennamen für den Datenbanknamen und den Servernamen wie im ursprünglichen Visual Studio Projekt bei den Parametern.

Nun kann man die Umgebung mit dem Projekt/Paket im Katalog verknüpfen: In den „Projekt/Paket“-Einstellungen unter „Verweise“ eine Umgebung auswählen und dem Projekt/Paket zuweisen.

Anschließend in den Parametern, die Umgebungsvariable als neue Wertquelle einsetzen.

Dann machen wir das Gleiche noch mit dem Servernamen.

SQL-Agent-Job

Wenn man nun einen SQL-Agent-Job definiert, dort den Schritt als Typ: „SQL Agent Integration Services-Paket“ auswählt und sich zu dem Paketserver und dem Paket verbindet, kann man unter Konfiguration die Umgebung auswählen, unter der das Paket gestartet werden soll.

Fehleranalyse

In diesem Abschnitt wird auf die Fehlerbehandlung eingegangen. Diese stellt sich nämlich ein wenig anders da, als man es bisher gewohnt war.

Wir haben in den vorangegangenen Abschnitten einen künstlichen, sehr einfachen Fehlerfall, innerhalb eines SSIS-Pakets, erzeugt. Wir würden bei der Ausführung des eingerichteten SQL-Agent-Jobs nun erwarten, dass wir den Fehler innerhalb des Protokolldatei Viewers des SQL-Agent-Jobs auslesen können.

Dies ist nicht der Fall, wie im oberen Bild zu erkennen, sondern es wird auf den „Integration Service Katalog“ verwiesen. Den genauen Grund für den Abbruch des SSIS-Pakets kann man hier nicht ersehen.

Der Integration Service Katalog stellt hierfür Reports bereit, die nun erläutert werden.

Die vordefinierte Fehleranalyse kann man über das Kontextmenü der Projekte (bzw. Pakete) innerhalb des Integration Service Katalogs erreichen.

Im folgenden Fenster bekommt man eine Übersicht aller Ausführungen des Paketes oder bei einem Projektbericht alle Ausführungen aller Pakete innerhalb des Projekts.

Innerhalb jeder Zeile erkennt man, dass drei Links einen Weg zu einer weiteren Analyse ermöglichen. Den Link „Alle Meldungen“ werden wir im Folgenden genauer erläutern.

„Alle Meldungen“

In diesem Report kann der eigentliche Abbruchgrund ermittelt werden. Der Report gliedert sich in 3 Abschnitte.

Im oberen Abschnitt werden allgemeine Ausführungsinformationen angezeigt.

Im mittleren Abschnitt werden fehlerhafte Paketschritte angezeigt und somit z. B. der Grund für einen Paket-Abbruch.

Im unteren Abschnitt kann man in einer detaillierten Auflistung die im Paket/Projekt ausgeführten Schritte analysieren.

Innerhalb der Spalte „Meldung“ kann man nun den genauen Grund für den Abbruch ermitteln, dieser ist somit die Division durch NULL.

Fehleranalyse innerhalb der SSIDB

Alternativ zu den Standardreports kann man auch Analysen direkt auf die SSISDB machen. Mit der folgenden Abfrage kann nach einem Ausführungsfehler innerhalb der SSISDB gesucht werden. Wenn man den Filter auf den „Message_Type“ weglässt, bekommt man eine Übersicht über alle Ausführungen.

Fazit

Mit dem „Integration Service Katalog“ hat man ein sehr mächtiges Werkzeug in der Hand, um die Verwaltung und die Analyse von SSIS-Paketen durchzuführen.

Einziger Nachteil bleibt, dass die Pakete, wenn sie einmal in den „Integration Service Katalog“ hochgeladen wurden, nicht mehr extrahiert, analysiert oder verändert werden können. Somit ist für den Einsatz des „Integration Service Katalog“ eine gute Quellcodeverwaltung unablässig.