Wer kennt sie nicht, die Benachrichtigung, dass der Prozess zur Datenaufbereitung fehlgeschlagen ist. Wünschenswert wäre es, sofort detaillierte Informationen über die Fehlerursache zu erhalten, besser noch, der verantwortlichen Fachabteilung die fehlerhaften Datensätze ohne eigenes Zutun aufzeigen zu können.
Ein Ansatz ist, eine Textdatei mit allen fehlerhaften Datensätzen inklusive Fehlerbeschreibung per E-Mail an die verantwortliche Fachabteilung zu senden.
Dieser Blogbeitrag zeigt, wie mit Microsoft Standardtools eine automatisierte Fehler-E-Mail mit detailliertem Inhalt generiert wird.
Szenario
Per SQL Server Integration Services (SSIS) werden Datensätze zu Bestellungen, Bestellpositionen und Zahlungseingängen jeweils im .txt-Dateiformat (nachfolgend auch als Flat Files bezeichnet) importiert. Die Importdateien Bestellungen.txt und Bestellpositionen.txt fallen in den Verantwortungsbereich der Einkaufsabteilung. Die Importdatei Zahlungseingänge.txt liegt im Verantwortungsbereich des Con-trollings.
Die Stammdaten zu den Artikeln aus der Tabelle Artikel werden aus der SQL-Datenbank Vorsystem_DB importiert. Die Korrektheit dieser Daten fällt in den Verantwortungsbereich der Abteilung Produktpflege.
Als Ziel dient die SQL-Datenbank Test_DB.
Sollten sich fehlerhafte Datensätze in den Flat Files oder der Tabelle Artikel befinden, werden diese inklusive Fehlerbeschreibung (im Folgenden als Fehlerreports bezeichnet) automatisch als Textdatei per E-Mail an die verantwortlichen Fachabteilungen gesendet. Ursachen für fehlerhafte Datensätze sind z. B. falsche Datumsformate, Dateneinträge, die nicht dem definierten Datentyp der Zielspalte entsprechen oder deren Länge an Zeichen übersteigen, NULL-Werte oder auch Dimensionselemente in den Faktentabellen, welche nicht in den Stammdaten gepflegt sind.
Kommt es zu einem Fehler in der Prozedur P_Transform_All, werden die durch den DeltaMaster Modeler dokumentierten Fehlerbeschreibungen automatisch als Fehlerreport per E-Mail an die verantwortlichen Ansprechpartner gesendet.
Voraussetzungen
Die in diesem Blogbeitrag beschriebene Vorgehensweise und gezeigten Screenshots beziehen sich auf die Arbeit mit Microsoft Visual Studio Professional 2015 und müssen bei der Verwendung abweichender Versionen eventuell angepasst werden.
Voraussetzung für den automatischen Versand von E-Mails aus einem SSIS-Paket ist zunächst ein Verbindungsmanager zu einem SMTP-Server, welcher Anfragen zum E-Mail-Versand entgegennimmt (siehe Abbildung 1).
Abbildung 1: Verbindungsmanager zu einem SMTP-Server
Zudem wird ein grundlegendes Verständnis in der Arbeit mit folgenden Komponenten in SSIS vorausgesetzt:
- Task ‚Mail senden‘
- Ereignishandler
- Fehlerbehandlung in Datenflusstasks
- Skriptkomponenten
Zur leichteren Administration und besseren Übersichtlichkeit im SSIS-Paket wird empfohlen, alle Tasks und Komponenten im Hinblick auf eine E-Mail-Empfängergruppe bzw. verantwortliche Fachabteilung in einen eigenen Sequenzcontainer zu verschieben.
Des Weiteren empfiehlt sich die Verwendung von Variablen für die Verbindungszeichenfolgen zu allen Importdateien und Fehlerreports. Zusätzlich werden Variablen für die E-Mail-Anhänge sowie für das Auslösen des E-Mail-Versands (Trigger) benötigt (siehe Abbildung 2).
Abbildung 2: Variablen im SSIS-Paket
Import von Flat Files
Das hier beschriebene Vorgehen lässt sich auf den Import von Dateien im Format .csv, .xls und .xlsx übertragen. Lediglich die Objekte der Datenquellen müssen dann ausgetauscht werden.
Im hier gezeigten Szenario stellt die Abteilung Einkauf die beiden Flat Files Bestellungen.txt und Be-stellpositionen.txt bereit. Für beide Importdateien wird je ein Datenflusstask erstellt. Dies ist notwendig, um später gezielt nur für die fehlerhaften Importdateien Fehlerreports zu versenden.
Nachfolgend ein Beispiel der Flat File Bestellungen.txt mit dem fehlerhaften Datensatz in Zeile 1.
Abbildung 3: Flat File Bestellungen.txt
Die Datenflusstasks bestehen aus je einer Flatfilequelle und einem OLE DB-Ziel (die nachfolgend auf-geführten Screenshots bzgl. der Bestellungen gelten auch für die Bestellpositionen).
Abbildung 4: Datenflusstask Datenimport Bestellungen
Sollten in der Flatfilequelle Bestellungen Fehler auftreten, so wird ein nachgelagerter Datenflusstask Datenimport Bestellungen Selektiv ausgelöst (siehe Abbildung 5).
Abbildung 5: Auslösung des Datenflusstask Datenimport Bestellungen Selektiv bei Fehler
Durch die zweistufige Datenimportlogik wird erreicht, dass der E-Mail-Versand mit Fehlerreports nur im Fehlerfall angestoßen wird.
Die Verbindungsmanager zu den Fehlerreports sind folgendermaßen zu konfigurieren:
- Registerkarte Allgemein: Format: Mit Trennzeichen & Spaltennamen in der ersten Datenzeile aktivieren
- Registerkarte Spalten: Spaltentrennzeichen z.B. |
- Registerkarte Erweitert: Spalte Datensatz als Datentyp Textdatenstrom [DT_TEXT] & Spalte Fehler als Datentyp Unicode-Zeichenfolge [DT_WSTR] mit einer ausreichenden Länge von Zei-chen (z.B. 255)
Der Datenflusstask Datenimport Bestellungen Selektiv schreibt die fehlerfreien Datensätze in die Im-porttabelle T_Import_Bestellungen (Datenbank Test_DB). Fehlerhafte Datensätze werden in der Text-datei Fehlerreport Bestellungen.txt abgelegt (siehe Abbildung 6).
Abbildung 6: Datenflusstask Datenimport Bestellungen Selektiv
In der Fehlerausgabe der Flatfilequelle wird das Umleiten von Zeilen definiert, wenn
- Fehler in den Quellspalten auftreten
- Daten in den Quellspalten aufgrund einer zu langen Zeichenkette abgeschnitten werden
Abbildung 7: Fehlerausgabe der Flatfilequelle
Die Skriptkomponente Fehlerbeschreibung Bestellungen ermitteln übersetzt den von der Flatfilequelle übergegebenen Fehlercode (ErrorCode) in die entsprechende Fehlerbeschreibung und schreibt diesen in die Ausgabespalte Fehler. Als Skript-Sprache kann Microsoft Visual Basic 2012 verwendet werden.
Abbildung 8: Übergabe der Spalte ErrorCode
- Prozedur zur Ermittlung der Fehlerbeschreibung:
Bei der Definition der Ausgabespalte Fehler sollte darauf geachtet werden, diese als Unicode-Zeichenfolge [DT_WSTR] mit einer ausreichenden Anzahl an Zeichen zu deklarieren (z.B. 255) (siehe Abbildung 9).
Abbildung 9: Definition der Ausgabespalte Fehler
Im Flatfileziel Fehlerreport Bestellungen sollte die Option Daten in Datei überschreiben aktiviert sein (siehe Abbildung 10). Dies bewirkt, dass alte Einträge automatisch gelöscht werden.
Abbildung 10: Option Daten in Datei überschreiben
Der automatische E-Mail-Versand wird über einen Ereignishandler OnPostExecute für den Datenflusstask Datenimport Bestellungen Selektiv ausgeführt (siehe Abbildung 11). Der Ereignishandler OnPostExecute wird erst nach dem Abschluss des Tasks oder Containers, in dessen Kontext er definiert ist, aktiviert.
Abbildung 11: Ereignishandler OnPostExecute
Der Task ‚Ausdruck‘ Anhang Einkauf übergibt die Verbindungszeichenfolge des Fehlerreports Fehlerreport Bestellungen.txt aus der Variablen Fehlerreport_Bestellungen an die Variable Anhang_Einkauf (siehe Abbildung 12).
Abbildung 12: Task ‚Ausdruck‘ Anhang Einkauf
Durch den Task ‚Mail senden‘ E-Mail mit Fehlerreport an Einkauf wird die E-Mail an die verantwortliche Fachabteilung Einkauf geschickt. In den Expressions wird der E-Mail-Anhang durch die Variable Anhang_Einkauf definiert. Im E-Mail-Betreff können mit Hilfe der Systemvariablen MachineName und PackageName der Name des Servers und des SSIS-Pakets angezeigt werden (siehe Abbildung 13).
Abbildung 13: Task ‚Mail senden‘ E-Mail mit Fehlerreport an Einkauf
Im Ergebnis erhalten die Ansprechpartner der verantwortlichen Fachabteilung eine E-Mail mit einer Textdatei im Anhang, welche alle fehlerhaften Datensätze bzgl. des Imports, inklusive der entsprechenden Fehlermeldungen, enthält (siehe Abbildungen 14 und 15).
Abbildung 14: E-Mail mit Fehlerreport Bestellungen
Abbildung 15: Fehlerreport Bestellungen
Import von Daten aus Vorsystemen
In diesem Abschnitt werden Stammdaten zu Artikeln aus der Tabelle Artikel einer SQL-Datenbank Vorsystem_DB importiert. Die Abteilung Produktpflege stellt sicher, dass die Daten im definierten Format geliefert werden.
Zu Beginn wird ein Datenflusstask mit einer OLE DB-Quelle (Tabelle Artikel in Datenbank Vorsystem_DB) und einem OLE DB-Ziel (Tabelle T_Import_Artikel_Temp in Datenbank Test_DB) erstellt (siehe Abbildung 16).
Abbildung 16: Datenflusstask Datenimport Artikel
Anschließend wird per Task ‚SQL ausführen‘ Datenvalidierung die Prozedur P_Import_Artikel aufgerufen, welche die fehlerfreien Datensätze (Artikel_ID besitzt fünf Zeichen) aus Tabelle T_Import_Artikel_Temp in die Tabelle T_Import_Artikel und die fehlerhaften Datensätze (Artikel_ID besitzt nicht fünf Zeichen) mit der Fehlermeldung Ungültige Artikel_ID in die Tabelle T_Import_Artikel_Fehler schreibt (siehe Abbildung 17).
Abbildung 17: Task ‚SQL ausführen‘ Datenvalidierung
- Prozedur P_Import_Artikel:
CREATE PROCEDURE [dbo].[P_Import_Artikel] AS
INSERT INTO [T_Import_Artikel]
SELECT
[Artikel_ID]
,[Artikel_BEZ]
FROM [T_Import_Artikel_Temp]
WHERE LEN([Artikel_ID]) = 5
INSERT INTO [T_Import_Artikel_Fehler]
SELECT
[Artikel_ID]
,[Artikel_BEZ]
,'Ungültige Artikel_ID'
FROM [T_Import_Artikel_Temp]
WHERE LEN([Artikel_ID]) <> 5
OR [Artikel_ID] IS NULLBevor nun die fehlerhaften Datensätze in die Datei Fehlerreport Artikel.txt geschrieben werden können, muss diese Datei zunächst geleert werden, damit später keine fehlerhaften Datensätze zurückliegender Importprozesse versendet werden. Das geschieht über eine Batch-Datei FehlerreportArtikel-NeuErstellen.bat, welche die Datei Fehlerreport Artikel.txt löscht und mit leerem Inhalt neu erstellt (siehe Abbildung 18).
Abbildung 18: .bat-Datei FehlerreportArtikelNeuErstellen.bat
Aufgerufen wird die FehlerreportArtikelNeuErstellen.bat über den Task ‚Prozess ausführen‘ Fehlerreport Neu erstellen. Der SuccessValue muss von 0 auf 1 gestellt werden (siehe Abbildung 19).
Abbildung 19: Task ‚Prozess ausführen‘ Fehlerreport Neu erstellen
Anschließend werden per Datenflusstask Fehlerhafte Datensätze wegschreiben alle Datensätze aus der Tabelle T_Import_Artikel_Fehler in die Datei Fehlerreport Artikel.txt geschrieben (siehe Abbildung 20).
Abbildung 20: Datenflusstask Fehlerhafte Datensätze wegschreiben
Da eine E-Mail an die verantwortliche Fachabteilung nur im Falle fehlerhafter Datensätze versendet werden soll, wird eine derartige Prüfung benötigt. Das lässt sich über eine Variable Trigger_Fehlerreport_Artikel realisieren. Hierzu wird ein Task ‚SQL ausführen‘ Trigger E-Mail-Versand erstellt, welcher per SQL-Direkteingabe die Anzahl an Datensätzen der Tabelle T_Import_Artikel_Fehler ausliest (siehe Abbildung 21).
Abbildung 21: Task ‚SQL ausführen‘ Trigger E-Mail-Versand
Im Resultset wird schließlich die Anzahl an Datensätzen an die Variable Trigger_Fehlerreport_Artikel übergeben:
Abbildung 22: Überschreiben der Variablen Trigger_Fehlerreport_Artikel
Die nachfolgend gelagerten Tasks ‚Ausdruck‘ Anhang Produktpflege und ‚Mail senden‘ E-Mail mit Fehlerreport an Produktpflege sollen nur ausgeführt werden, wenn mindestens ein fehlerhafter Datensatz erkannt wurde. Diese Prüfung übernimmt die Rangfolgeeinschränkung durch den Auswertungsvorgang Ausdruck und der Prüfung Trigger_Fehlerreport_Artikel > 0 (siehe Abbildungen 23 und 24).
Abbildung 23: Rangfolgeeinschränkung mit Auswertungsvorgang Ausdruck
Abbildung 24: Rangfolgeeinschränkungs-Editor
Die darauffolgenden Tasks ‚Ausdruck‘ Anhang Produktpflege und ‚Mail senden‘ E-Mail mit Fehlerreport an Produktpflege verhalten sich analog zu den Definitionen bzgl. der Bestellungen in Kapitel 3 mit dem Unterschied, dass sie sich jetzt nicht im Kontext eines Ereignishandlers, sondern direkt in der Ablaufsteuerung befinden (siehe Abbildungen 25 und 26).
Abbildung 25: Task ‚Ausdruck‘ Anhang Produktpflege
Abbildung 26: Task ‚Mail senden‘ E-Mail mit Fehlerreport an Produktpflege
Im Ergebnis erhalten die Ansprechpartner der verantwortlichen Fachabteilung Produktpflege eine E-Mail mit einer Textdatei im Anhang, welche alle fehlerhaften Datensätze bzgl. des Import, inklusive der entsprechenden Fehlermeldungen, enthält:
Abbildung 27: E-Mail mit Fehlerreport Artikel
Abbildung 28: Fehlerreport Artikel
Doppelte Prüfung von Flat Files
Die Prüfung von Importdateien aus Kapitel 3 lässt sich auch mit der relationalen Prüfung von Datensätzen aus Kapitel 4 verbinden. Hierfür erfolgt nach der zweistufigen Datenimportlogik für Flat Files mit zwei Datenflusstasks anschließend ein Task ‚SQL ausführen‘ Datenvalidierung. Damit der Task Datenvalidierung ausgeführt werden kann, müssen die beiden Rangfolgeeinschränkungen, welche von den beiden Datenflusstasks zum Task ‚SQL ausführen‘ führen, für den Wert Erfolg disjunkt zueinander gesetzt werden, da jeweils nur einer der beiden Datenflusstasks erfolgreich abgeschlossen werden kann (hierfür beide Rangfolgeeinschränkungen markieren und im Eigenschaftenfenster anpassen) (siehe Abbildung 29).
Abbildung 29: Rangfolgeeinschränkungen disjunkt setzen
indem fehlerfreie Datensätze in die Tabelle T_Import_Zahlungseingänge geschrieben werden, wäh-rend fehlerhafte Datensätze in die Datei Fehlerreport Zahlungseingänge.txt importiert werden. Dieser Fehlerreport wird über den Ereignishandler OnPostExecute im Kontext des Datenflusstasks Datenim-port Zahlungseingänge Selektiv an die verantwortliche Fachabteilung Controlling versendet (siehe Kapitel 3).
Anschließend wird via Task ‚SQL ausführen‘ Datenvalidierung eine SQL-Prozedur P_Import_Zahlungseingänge_Unbekannte_Kunden aufgerufen, welche überprüft, ob Zahlungseingänge mit bisher für die Kundendimension unbekannten Kunden importiert wurden (siehe Abbildung 30).
Abbildung 30: P_Import_Zahlungseingänge_Unbekannte_Kunden
Hier ist das der Zahlungseingang für die KundeID 105. Dieser Datensatz wird nun äquivalent zu dem beschriebenen Vorgehen in Kapitel 4 zunächst in eine Datenbanktabelle T_Import_Zahlungseingänge_Unbekannte_Kunden importiert und anschließend in einem Datenflusstask Fehlerhafte Datensätze wegschreiben in die Datei Fehlerreport Zahlungseingänge mit unbekannten Kunden.txt geschrieben.
Die folgenden Tasks ‚SQL ausführen‘ Trigger E-Mail-Versand‚ ‚Ausdruck‘ Anhang Controlling und ‚Mail senden‘ E-Mail mit Fehlerreport an Controlling verhalten sich analog zu den Definitionen bzgl. der Artikel in Kapitel 4.
Die beiden E-Mails mit den Fehlerreports im Anhang an die verantwortliche Fachabteilung Controlling sehen nun wie folgt aus:
Abbildung 31: E-Mail mit Fehlerreport Zahlungseingänge
Abbildung 32: Fehlerreport Zahlungseingänge
Abbildung 33: E-Mail mit Fehlerreport Zahlungseingänge mit unbekannten Kunden
Abbildung 34: Fehlerreport Zahlungseingänge mit unbekannten Kunden
Fehler in Prozedur Transform All
Sollten sich trotz Datenprüfung der Importdaten fehlerhafte Datensätze im Modell befinden, so wird die Prozedur P_Transform_All diese erkennen und fehlschlagen. In diesem Fall gilt es, die Datensätze zu analysieren und zu bereinigen. Die durch den DeltaMaster Modeler dokumentierten Fehlermeldungen lassen sich in eine View V_Import_Fehlermeldungen_Transform_All schreiben (siehe Abbildung 35).
Abbildung 35: V_Import_Fehlermeldungen_Transform_All
Anschließend werden äquivalent zu dem beschriebenen Vorgehen in Kapitel 4 in einem Datenflusstask Fehlermeldungen wegschreiben die dokumentierten Fehlermeldungen in eine Datei Fehlerreport Transform All.txt geschrieben.
Die danach folgenden Tasks ‚SQL ausführen‘ Trigger E-Mail-Versand‚ ‚Ausdruck‘ Anhang Bissantz und ‚Mail senden‘ E-Mail mit Fehlerreport an Bissantz orientieren sich an den Definitionen der Artikel in Kapitel 4.
Die E-Mail mit dem Fehlerreport im Anhang sieht wie folgt aus:
Abbildung 36: E-Mail mit Fehlerreport Transform All
Abbildung 37: Fehlerreport Transform All
In der Ablaufsteuerung des SSIS-Pakts ist darauf zu achten, dass für die Rangfolgeeinschränkungen, welche den Task ‚SQL ausführen‘ P_Transform_All ansprechen, der Wert Beendigung gesetzt ist, da die vorgelagerten Sequenzcontainer im Falle fehlerhafter Datensätze in Verbindung mit dem Import von Flat Files fehlschlagen können. Abbildung 38 zeigt die komplette Ablaufsteuerung des SSIS-Pakets nach der Ausführung.
Abbildung 38: Komplette Ablaufsteuerung des SSIS-Pakets
