SSIS vs. SQL – Quellcodeverwaltung

marcus

This Article in English…

Überblick

SQL Server Integration Services (SSIS) ist ein äußerst mächtiges Tool Set für die Entwicklung von ETL-Strecken. Es gibt viele gute Gründe, die für einen Einsatz von SSIS sprechen. Es gibt derer aber auch genügend, die dagegen sprechen. Beschränken wir uns auf den Microsoft Produkt Stack, dann kommt als Alternative für die Entwicklung von komplexen ETL Strecken (im Wesentlichen) nur noch Transact-SQL (T-SQL) in Frage.

Dieser Artikel gehört zu einer Serie von Artikeln, die wichtige Entscheidungskriterien für die Wahl der richtigen Technologie(n) – SSIS und/oder T-SQL – beleuchten.

—

Eine Quellcodeverwaltung ermöglicht die Speicherung von Quellcode in verschiedenen Versionen. Nach einer Änderung kann die geänderte Datei als neue Version gespeichert werden. Mit der geänderten Version der Datei werden auch Metadaten wie Datum und Uhrzeit der Änderung, Benutzerkennung der Person die die Änderung durchgeführt hat, Verweis auf einen Change-Request, etc. gespeichert. Ältere Versionen eines Dokumentes können damit wieder hergestellt werden.

Darüber hinaus bieten Quellcodeverwaltungen zusätzliche Features an, die unter anderem für die Arbeit in einem Mehrentwicklerteam und auch für das Releasemanagement unerlässlich sind. Kurz: Die Verwaltung von Quellcode ist essentieller Bestandteil professioneller Softwareentwicklung. Bekannte Quellcodeverwaltungssysteme im Microsoft-Umfeld sind der Team Foundation Server (TFS) bzw. Azure DevOps Server oder Git und können in die Entwicklungsumgebung Visual Studio integriert werden.

Ein weiteres wichtiges Feature einer Quellcodeverwaltung ist der Vergleich zweier Versionen einer Datei. Über einen Vergleich zweier Versionen der gleichen Datei können die Unterschiede identifiziert werden. Ein Anwendungsfall hierfür ist zum Beispiel das 4-Augen-Prinzip. Ein Entwickler arbeitet an einem Dokument und ein weiterer führt einen Review des geänderten Dokumentes durch. Relevant für den Review sind nur die Änderungen in der neuen Version und diese können über einen Versionsvergleich ermittelt werden.

Der Vergleich von zwei Versionen einer Datei ist allerdings nur dann hilfreich, wenn aus den ermittelten Änderungen die Bedeutung und ggf. der Grund der Änderung ersichtlich sind. Ein Vergleich von Binär-Dateien ist in der Regel nicht hilfreich, da weder die Bedeutung der Änderung noch der Grund erkennbar sind. Bei einem Vergleich von zwei Versionen einer Text-Dateien ist die Wahrscheinlichkeit hoch, dass beides erkennbar ist. Etwas schwieriger wird es allerdings auch hier, wenn die Daten in der Text-Datei hierarchisch und strukturiert gespeichert sind. So werden SSIS-Pakete in einer hierarchisch strukturierten Weise in XML-Dateien mit der Extension *.dtsx gespeichert, während SQL Statements üblicherweise als (reine) Text-Dateien mit der Extension *.sql gespeichert werden.

Dieser Artikel beschreibt den Versions-Vergleich von SQL-Skripten und SSIS-Paketen anhand von drei Beispielen.

  • Vergleich von zwei Versionen eines SQL Statements
  • Vergleich von zwei Versionen eines einfachen SSIS-Paketes
  • Vergleich von komplexen Entwicklungsartefakten

Vergleich von zwei Versionen eines SQL-Skriptes

Die folgende Abbildung zeigt das Ergebnis eines Vergleichs von zwei Versionen eines SQL-Skriptes in Visual Studio. In diesem Vergleich werden die Abweichungen farblich hervorgehoben.

Der Screenshot zeigt auf der linken Seite das SQL Statement vor der Änderung und auf der rechten Seite das geänderte SQL Statement. In dem geänderten SQL Statement sind Zeilen, die eine Änderung enthalten, hellgrün hinterlegt. Änderungen selbst werden in einem kräftigeren Grün hervorgehoben. In der Vorgängerversion auf der linken Seite sind die korrespondierenden Texte in roter Farbe hinterlegt.

Rechts neben dem vertikalen Scroll-Balken sind die geänderten Bereiche in dem gesamten Dokument angedeutet. Eine gute Beschreibung zu der Verwendung des Datei-Vergleichs findet sich in der Online Dokumentation von Microsoft.

Vergleich von zwei Versionen eines einfachen SSIS-Paketes

Die Entwicklung von ETL-Strecken mit SSIS erfolgt in SSIS-Paketen. SSIS-Pakete werden als XML-Dokument gespeichert. Microsoft hat in der Online Dokumentation mit Bezug zu der Dokumentation von SSIS-Paketen den folgenden Artikel veröffentlicht: SSIS-Paketformat und führt darin unter anderem aus:

In der aktuellen Version von Integration Services wurden wichtige Änderungen am Paketformat (DTSX-Datei) vorgenommen, um das Format besser lesbar zu machen und Pakete besser vergleichen zu können. Außerdem wurde das Zusammenführen von Paketen verbessert, die keine miteinander in Konflikt stehenden Änderungen oder im Binärformat gespeicherte Änderungen enthalten.

Dieses Zitat ist dem ersten (!) Abschnitt des Artikels entnommen. Demnach wurde das Format weiterentwickelt, um unter anderem Pakete besser vergleichen zu können.

Die folgenden beiden Screenshots zeigen zwei Versionen eines Control Flows eines SSIS-Paketes, in dem lediglich der Name der zweiten Skript-Task von B SCT Skripttask nach D SCT Skripttask geändert wurde. Die Screenshots wurden von einem SSIS-Paket genommen, in dem Tasks einfach in dem Control Flow platziert, beliebig benannt, miteinander verbunden aber nicht weiter konfiguriert. Das Beispiel ist ein einfaches und daher nur plakatives Beispiel.

Version 1

Version 2

Versionsvergleich

Der Vergleich beider Versionen bringt Erstaunliches zu Tage. Die Änderung des Namens einer Skript Task resultiert in sagenhaften 8 geänderten Bereichen des SSIS Paketes/XML-Dokumentes, die rechts neben der vertikalen Scroll-Bar angezeigt werden.

Die Zeilen 60 bis 77 repräsentieren (unter anderem) die Skripttask B SCT Skripttask in der Vorgänger Version. Diese Skripttask wurde gemäß Vergleich nach C SCT Skripttask umbenannt und nicht nach D SCT Skripttask. Die Zeilen 78 bis 95 repräsentieren (unter anderem) die Skripttask C SCT Skripttask in der Vorgänger Version. Diese Skripttask wurde laut Vergleich nach D SCT Skripttask umbenannt.

Noch mal zur Erinnerung: Geändert wurde lediglich der Name der Skripttask B SCT Skripttask nach D SCT Skripttask.

Der Vergleich liefert hier schlicht ein falsches Ergebnis.

Vergleich von komplexen Entwicklungsartefakten

In diesem Abschnitt wird ein etwas komplexeres Beispiel betrachtet, das in einer ähnlichen Form durchaus in der Praxis zu finden sein könnte.

Aufgabe

In diesem Beispiel ist das Ranking der Mitarbeiter in der Tabelle [AdventureWorksDW2017].
[DimEmployee] je Hierarchie-Stufe entlang der Urlaubszeiten sowie Krankheitszeiten der Mitarbeiter zu ermitteln. Zu jedem Mitarbeiter sind vier Kennzahlen zu ermitteln. Für die Berechnung der Kennzahlen ist die Methodologie der bei den Kennzahlen angegeben SQL Server Aggregats Funktionen (Windowed Function) zu verwenden:

  • Ranking Urlaubszeit: NTILE(3)
    NTILE(3) teilt die zu beurteilende Menge der Mitarbeiter (je Hierarchie-Stufe) in drei gleich große Gruppen ein. Die Zuordnung des Mitarbeiters zu einer der drei Gruppen erfolgt entsprechend der aufsteigenden Sortierung nach Urlaubszeiten.
  • Ranking Urlaubszeit: DENSE_RANK
    DENSE_RANK ermittelt zu jedem Mitarbeiter (je Hierarchie-Stufe) eine Position in einer Rangfolge. Liegen zu zwei Mitarbeitern z.B. die gleiche Anzahl Urlaubsstunden vor, erhalten beide Mitarbeiter die gleiche Position im Ranking.
  • Ranking Krankheitszeit: NTILE(3)
    Analog zu Urlaubszeit.
  • Ranking Krankheitszeit: DENSE_RANK
    Analog zu Urlaubszeit.

Diese Aufgabe ist sowohl über ein SQL Statement als auch über ein SSIS-Paket zu lösen. In einem zweiten Teil sind die so entwickelten Dokumente dahingehend zu ändern, dass als Gruppierungskriterium nicht mehr die Urlaubs- und Krankheitszeiten, sondern das Einstellungsdatum des Mitarbeiters sowie sein Geburtsdatum zu verwenden ist.

Bei dieser Aufgabe gibt zwei Herausforderungen:

  • Ermittlung der Hierarchie der Mitarbeiter
  • Ermittlung des Rankings

SQL Statement

Transact-SQL stellt für beide Herausforderungen leicht zu verwendende Methoden vor:

Sind Daten – wie in der Tabelle [DimEmployee] – über eine Vater-Kind-Beziehung strukturiert, können diese Daten über eine CTE sehr effizient rekursiv ausgewertet werden:

Source

Dieses Statement liefert zu jedem Mitarbeiter in der Spalte [Level] die Hierarchiestufe sowie die geforderten Kennzahlen entlang der Urlaubszeiten sowie Krankheitszeiten. Insgesamt sind die 254 Mitarbeiter in 5 Hierarchiestufen organisiert.

Der Vergleich des Statements aus der Abbildung 5: Beispiel 3 – SQL Statement mit der geänderten Version wurde bereits in Abbildung 1: Versionsvergleich eines SQL Satetments über Visual Studio dargestellt. Daher wird hier auf eine weitere Abbildung des geänderten Statements verzichtet.

Beide Statements sind über die folgenden beiden Links abrufbar:

SSIS-Paket

So einfach diese Aufgabe über T-SQL zu lösen war, so schwer und vor allem zeitraubend war die Entwicklung der Lösung in SSIS, wenn man für die Lösung ausschließlich die Werkzeuge von SSIS verwenden möchte. Für die hier dargestellte Lösung wurde der folgende Ansatz gewählt:

  • Je Aufgabe werden zwei Tabellen in der Datenbank benötigt.
  • Der Dataflow 1000 DFT Calculate Levels ermittelt ausschließlich die Hierarchie.
  • Der Dataflow 3000 DFT Caculate Ranking ermittelt ausschließlich das Ranking und speichert das Ergebnis in einer Tabelle.
  • Das Ranking wird für Urlaubs-/Krankheitszeiten jeweils über zwei Skript-Tasks ermittelt.

Möglicherweise gibt es auch eine viel einfachere Lösung.

Control Flow

Der Control-Flow des SSIS-Paktes gestaltet sich mit 4 Tasks noch recht einfach…

Data Flow 1000 DFT Calculate Levels

Während die Lösung über T-SQL einen rekursiven Ansatz unterstützt, ist dieses bei SSIS (nach meinem Kenntnisstand) nicht möglich. Die Zuordnung der 5 Hierarchiestufen muss daher je Stufe entwickelt werden.

Data Flow 3000 DFT Calculate Ranking

Die eigentliche Berechnung des Rankings erfolgt in Skript-Tasks durch Vergleich zwei aufeinander folgender Datensätze…

Anpassung des Paketes

Nach Fertigstellung des Paketes, wurde das Paket in Team Foundation Server (TFS) eingecheckt und anschließen so abgeändert, dass als Ordnungskriterium für die Ermittlung des Rankings nun nicht mehr Urlaubs-/Krankheitszeiten sondern Einstellungs-/Geburtstage verwendet werden. Im Wesentlichen sind hier drei Feldnamen und ein Datentyp zu ändern gewesen. Trotz der größeren Anzahl der verwendeten Tasks, hat sich der Umfang der Änderungen in Grenzen gehalten und war in wenigen Minuten erledigt. Das Ergebnis wurde ebenfalls in TFS eingecheckt.

Vergleich der zwei Versionen

Ein Vergleich der beiden in TFS eingecheckten Version ergab die folgenden Änderungen:

Maßgeblich für die Beurteilung des Umfangs der Änderung in dem SSIS Paket ist der rechte vertikale Scroll-Balken. Rechts neben dem Scroll-Balken sind die Fundstellen der Änderungen in dem SSIS-Paket farblich in grüner und roter Farbe hervorgehoben. Trotz des eher geringen Umfangs der Änderungen ist förmlich das gesamte SSIS Paket auf den Kopf gestellt worden.

Source Code

Die hier gezeigten Beispiele wurden unter Visual Studio 2017 in Verbindung mit einer SQL Server 2017 Datenbank entwickelt. Der Source Code zu dem Beispiel kann über den folgenden Link heruntergeladen werden:

Sourcen

Fazit

Während die Lösung der Aufgabe in T-SQL relativ schnell entwickelt werden kann, ist die Entwicklung des SSIS-Paketes aufwändig und sie hat mehrere Stunden Zeit gekostet. Grund hierfür waren im Wesentlichen die schlechte Lesbarkeit eines SSIS Paketes, aber auch die (überraschende) Erkenntnis, dass es für die vermeintlich einfache Aufgabe keinen OutOfTheBox-Lösungsansatz in SSIS gibt. Nach Fertigstellung und einem Checkin der Lösung waren nur noch vergleichsweise wenige Änderungen erforderlich, um das Ordnungskriteriums für die Berechnung der Kennzahlen zu ändern. Die wenigen Änderungen resultieren in zahlreichen Änderungen in dem zugrunde liegenden XML-Dokument. Ein Versions-Vergleich lässt den Entwickler ratlos zurück, was sich denn nun in dem SSIS Paket geändert hat. Zum Vergleich mit T-SQL: Hier ist zum einen der Lösungsweg direkt lesbar und Änderungen sind nachvollziehbar.

Am Ende bleibt nur ein Fazit möglich:

  • SSIS Pakete sind schwer lesbar
  • SSIS Pakete sind schwer wartbar
  • Ein Versionsvergleich von SSIS-Paketen ist nicht möglich

SSIS ist ein äußerst mächtiges Tool Set. Allerdings ist der Umfang der Verwendung bei fast jeder Aufgabe kritisch zu hinterfragen.