Die Konfigurationsmöglichkeiten für das Transaktionsverhalten in SSIS Paketen reichen in bestimmten Fällen nur bedingt aus. Stellen wir uns einmal folgende Ausgangssituation am Beispiel eines betriebswirtschaftlichen Szenarios vor: Wir benötigen ein SSIS-Paket zum Import der monatlichen Verteilungsschlüssel von Hauptkostenstellen auf Nebenkostenstellen. Um die Richtigkeit der Daten zu gewährleisten, prüfen wir diese Verteilung, bevor die Daten importiert werden. Wird eine Hauptkostenstelle nicht zu 100% verteilt, müssen wir auf diesen Fall reagieren.
Hierbei wird ersichtlich, dass es sich nicht um einen technischen Fehler handelt (der etwa unser Paket zum Absturz bringt), sondern um einen logischen Fehler, der bei Nicht-Berücksichtigung für größere Schwierigkeiten nach dem Import aufgrund inhaltlich fehlerhafter Daten sorgen kann. Aber auch unter Berücksichtigung der Problematik stellt sich die Frage:
Macht es Sinn, einen nur teilweise erfolgreichen Import durchzuführen?
Diese Frage lässt sich nicht pauschal beantworten, da je nach Szenario ein bestimmtes Verhalten erwünscht sein kann. Im produktiven Einsatz würde sie höchstwahrscheinlich mit einem klaren „nein” beantwortet werden, hingegen kann ein unvollständiger Import im Entwicklungs- und Testbetrieb in bestimmten Fällen durchaus sinnvoll sein.
Für genau solche Anforderungen haben wir ein Design-Pattern entwickelt, welches uns ermöglicht, ein transaktions-ähnliches Verhalten für inhaltliche, also nicht technische, Fehler innerhalb eines SSIS-Pakets per „Schalter” ein- und ausschalten zu können.
Dieses Verfahren werde ich Ihnen nun Schritt für Schritt vorstellen.
Am Ende des Beitrags finden Sie eine Übersicht der fertigen Lösung, die Ihnen hilft, das Gesamtbild nachzuvollziehen.
Werfen wir zunächst einen Blick auf die zu importierenden Quelldaten:
Die Hauptkostenstelle 4712 wird nicht zu 100% verteilt – auf diesen Fall möchten wir reagieren. Dazu gehen wir wie folgt vor:
Zu Beginn wird dem Quelldatenfluss eine neue Spalte JoinColumn mithilfe der DerivedColumn – Komponente (dt.: abgeleitete Spalte) hinzugefügt.
Den Wert setzten wir auf 0.
Dieses „Flag” dient im späteren Verlauf dazu, eine in jedem Fall wahre Join-Bedingung zu erzeugen, da die Integration Services standardmäßig keinen Cross-Join unterstützen. Hintergrund ist, dass nach der Fehlerüberprüfung aller Zeilen an jeder einzelnen Zeile abzulesen sein soll, ob irgendwann ein ungültiger Datensatz aufgetreten ist.
Mithilfe eines Conditional Split (dt.: bedingtes Teilen) trennen wir die gültigen (ID 2 und 3) von den ungültigen Datensätzen (ID 5 und 6).
Abhängig von Ihren Anforderungen, können sich in diesem Bereich auch mehrere komplexe, miteinander verkettete Operationen ansiedeln, um die gewünschte Trennung zwischen „gültig” und „ungültig” zu realisieren.
Am Ausgang der ungültigen Datensätze führen wir ein RowSampling (dt.: Zeilenstichprobe) mit folgenden Einstellungen durch:
Das RowSampling bewirkt, dass entweder keine oder maximal eine Zeile weitergeleitet werden. Es dient somit lediglich als Indikator, ob ungültige Datensätze aufgetreten sind oder nicht, unabhängig von ihrer Anzahl.
Als nächstes legen wir eine neue Variable namens FailOnError vom Datentyp Int32 an. Diese Variable entspricht dem oben angekündigten Schalter. Mit ihrer Hilfe schalten wir das Transaktionsverhalten ein oder aus.
Denkbar wäre hier ein erweitertes Szenario, in dem man durch Paketkonfiguration den Wert der Variable außerhalb des Pakets setzen kann (z.B. Web-Frontend).
In unserem Fall setzten den Wert initial auf 0 – das Transaktionsverhalten ist somit vorerst ausgeschaltet.
Nun fügen wir eine weitere Spalte Namens ErrorFlag mithilfe einer DerivedColumn am Ausgang der Zeilenstichprobe hinzu. Den Wert belegen wir mit dem der FailOnError-Variable, die je nach Konfiguration den Zustand 0 oder 1 hat ODER aber auch NULL sein kann, nämlich dann, wenn keine ungültigen Datensätze identifiziert wurden.
Im nächsten Schritt sortieren wir die Daten mithilfe der Sort-Komponente. Dieser Schritt ist notwendig, da die darauffolgende Merge-Join – Komponente nur mit Daten funktioniert, welche die Eigenschaft isSorted = true besitzen (diese Eigenschaft wird u.a. von der Sort-Komponente gesetzt).
Anschließend „joinen” wir den Wert des ErrorFlags mithilfe eines Merge-Joins (dt.: Zusammenführungsjoin) und unserer JoinColumn an die gültigen Datensätze (auch hier ist das „Vorschalten” der Sort-Komponente notwendig).
Die Konfiguration des Merge-Join’s sieht dabei folgendermaßen aus:
Am Ausgang des Merge-Join ??s sehen unsere Daten dann so aus:
Die letzte und gleichzeitig entscheidende Komponente ist ein Conditional Split mit folgender Konfiguration:
An dieser Stelle „trennt sich die Spreu vom Weizen” – Abhängig davon, welchen Wert die FailOnError Variable hat, werden hier entweder alle gültigen ODER keine Datensätze weitergeleitet.
Nicht vergessen: ändern Sie den Wert der Variable von 0 auf 1, um das Transaktionsverhalten einzuschalten.
Zu guter Letzt das versprochene Gesamtbild:
