Nat\u00fcrlich wollen wir auch wissen, ob der Prozess als Ganzes, eine Prozedur oder auch ein konkretes SQL-Statement erfolgreich ausgef\u00fchrt wurden.<\/p>\n\n\n\n
Die Vorgehensweise ist eigentlich gerade heraus: Zu Beginn jeder Aktion wird ein Protokolldatensatz geschrieben und nach der Beendigung der Aktion wird dieser entweder mit dem Status Erfolg oder Misserfolg und gegebenenfalls auch noch anderen hilfreichen Informationen aktualisiert. That’s it! Ein bisschen mehr darf es dann aber doch noch sein… <\/p>\n\n\n\n\n\n\n\n
Inhalt<\/h1>\n\n\n\n
\nDas Ergebnis<\/a> Auch wenn die Erstellung eines Protokolls an sich keine gro\u00dfe Sache ist bzw. sein sollte, gibt es doch einiges dazu zu schreiben. Fangen wir aber mit dem Ergebnis an und z\u00e4umen das Pferd von hinten auf. Die hier vorgestellte Prozessprotokollierung ist dreistufig und verwendet die folgenden Protokoll-Tabellen:<\/p>\n\n\n\n In diesen Tabellen werden ein ETL-Prozess und die damit verbundenen Komponenten und Arbeitsschritte mit wachsender Granularit\u00e4t – von oben nach unten gelesen – protokolliert. Der ETL-Prozess, die Komponenten und die einzelnen Arbeitsschritte werden mit genau einem Datensatz protokolliert. <\/p>\n\n\n\n Eng mit der Erstellung der Protokollierung des Prozesses verbunden ist nat\u00fcrlich auch die Protokollierung von Fehlern. Die hier vorgestellte Vorgehensweise f\u00fcr die Protokollierung verwendet die folgenden Tabelle f\u00fcr die Protokollierung von Fehlern:<\/p>\n\n\n\n Die folgenden Abbildungen zeigen das Ergebnis der dreistufigen Protokollierung eines einfachen, kompakten, aber vollst\u00e4ndigen ETL-Prozesses.<\/p>\n\n\n\n In der Tabelle [LL].[Trace]<\/strong> wird jede Aktion unter anderem mit dem Namen der Prozedur, der Zielentit\u00e4t, die von der Prozedur bearbeitet wird, einer kurzen Beschreibung, was konkret gemacht wurde und anderen Informationen, wie zum Beispiel die Anzahl der betroffenen Datens\u00e4tze und die Dauer der Ausf\u00fchrung protokolliert. <\/p>\n\n\n\n In der Tabelle [LL].[Component]<\/strong> werden die Aufrufe von Prozeduren und SSIS-Paketen oder – allgemein ausgedr\u00fcckt – Komponenten protokolliert. Auch hier wird eine kurze Beschreibung der Aufgabe der Komponente, die Zielentit\u00e4t und die Dauer der Ausf\u00fchrung protokolliert.<\/p>\n\n\n\n Auf der obersten Ebene wird jede Ausf\u00fchrung des ETL-Prozesses in der Tabelle [LL].[Execution]<\/strong> mit genau einem Datensatz protokolliert.<\/p>\n\n\n\n Alle Tabellen f\u00fcr die Prozessprotokollierung enthalten zwei Spalten [State]<\/strong> und [Success]<\/strong>, in denen der Erfolg oder Misserfolg der Ausf\u00fchrung einer Aktion, einer Komponente oder des ETL-Prozesses gespeichert wird.<\/p>\n\n\n\n Nach der kurzen Einf\u00fchrung des Ergebnisses sind die folgenden Aspekte der Vorgehensweise zu kl\u00e4ren:<\/p>\n\n\n\n Die Vorgehensweise sieht die Protokollierung eines ETL-Prozesses in den drei Tabellen [LL].[Execution]<\/strong>, [LL].[Component]<\/strong> und [LL].[Trace]<\/strong> vor. Jede der Tabellen ist f\u00fcr die Protokollierung von bestimmten Artefakten vorgesehen. Dieser Abschnitt stellt die Verwendung der jeweiligen Tabellen, ihre Spalten und den Code f\u00fcr ihre Erstellung vor.<\/p>\n\n\n\n In dieser Tabelle wird die Ausf\u00fchrung eines ETL-Prozesses mit genau einem Datensatz protokolliert. Ein ETL-Prozess hat immer eine Einstiegsfunktion. Das kann eine gespeicherte Prozedur, ein SSIS-Paket, ein Talend-Job oder aus ein SQL Server Agent<\/em> Job sein. Zu Beginn der Ausf\u00fchrung der Einstiegsfunktion wird ein Protokolldatensatz in diese Tabelle eingef\u00fcgt. Nach erfolgreicher Verarbeitung aller Aufgaben wird dieser Datensatz mit dem Status success <\/em>aktualisiert, im Fehlerfall mit dem Status error<\/em>. Damit enth\u00e4lt diese Tabelle eine \u00dcbersicht \u00fcber alle Ausf\u00fchrungen des ETL-Prozesses und stellt Informationen wie zum Beispiel den Status und die Dauer der Ausf\u00fchrung bereit. <\/p>\n\n\n\n In dieser Tabelle wird die Ausf\u00fchrung einer Komponente mit genau einem Datensatz protokolliert. Eine Komponente kann eine gespeicherte Prozedur, ein SSIS-Paket, ein Talendjob sein. Eine Komponente zeichnet sich dadurch aus, dass sie eine oder mehrere Datenmanipulationen steuert und ausf\u00fchrt. Wie schon bei der Tabelle [LL].[Execution]<\/strong> wird zu Beginn der Ausf\u00fchrung ein Protokolldatensatz in diese Tabelle eingef\u00fcgt und nach Beendigung mit dem Status success <\/em>oder dem Status error<\/em> aktualisiert. Damit enth\u00e4lt diese Tabelle je Ausf\u00fchrung des ETL-Prozesses eine Liste der ausgef\u00fchrten Komponenten und stellt Informationen wie den Status und die Dauer der Ausf\u00fchrung bereit.<\/p>\n\n\n\n Die Bezeichnung dieser Tabelle enth\u00e4lt schon den Hinweis, dass die Tabelle f\u00fcr die detailliertes Protokollierung der Aktionen des ETL-Prozesses vorgesehen ist, eben einen Trace erstellt. Welche Aktionen protokolliert werden ist eine Design-Entscheidung des Entwicklers. Es empfiehlt sich aber mindestens jedes INSERT- UPDATE und DELETE-Statement mit einem eigenen Protokolldatensatz zu protokollieren. Die Tabelle sieht neben den bereits oben erw\u00e4hnten Status-Feldern auch die Speicherung der Anzahl der betroffenen Datens\u00e4tze vor und gibt damit dem Entwickler die M\u00f6glichkeit zu beurteilen, ob die Statements genau das gemacht haben, was erwartet wurde.<\/p>\n\n\n\n Fehler, die in einem ETL-Prozess erkannt wurden, sind nat\u00fcrlich zu protokollieren. Zu unterscheiden ist die Protokollierung von Exceptions und Datenfehlern. Die Struktur dieser Tabelle ist auf die Protokollierung von Datenfehlern ausgelegt und enth\u00e4lt Spalten, in denen jeder Datenfehler vollst\u00e4ndig, les- und auswertbar protokolliert werden kann. Dieser Artikel legt den Fokus auf die Protokollierung eines ETL-Prozesses und nicht die Protokollierung von Datenfehlern. Die Protokollierung des Prozesses ist eng verbunden nicht einem expliziten Exception-Handling. Dem Exception-Handling und der Protokollierung von Exceptions ist ein separater Abschnitt gewidmet.<\/p>\n\n\n\n Der Schemaname LL <\/strong>steht f\u00fcr Logging Layer<\/em>. In diesem Schema sind alle Protokolltabellen und die gespeicherten Prozeduren, die f\u00fcr die Protokollierung verwendet werden, gespeichert.<\/p>\n\n\n\n Die folgenden Abschnitte beschreiben die Tabellen f\u00fcr die Protokollierung des Prozesses sowie die Tabelle f\u00fcr die Protokollierung von Fehlern. Abschlie\u00dfend findet sich ein Diagramm mit dem Datenmodell zu diesen Tabellen.<\/p>\n<\/div><\/div>\n\n\n\n Die Tabelle enth\u00e4lt die folgenden Spalten:<\/p>\n\n\n\n Die Tabelle wird \u00fcber die folgende Anweisung erstellt:<\/p>\n\n\n\n
\nUnd nun die Herleitung…<\/a>
\n\n Dreistufige Protokollierung<\/a>
\n [LL].[Execution]<\/a>
\n [LL].[Component]<\/a>
\n [LL].[Trace]<\/a>
\n Der Schemaname LL<\/a>
\n\n Tabellenbeschreibung, Deklaration und Datenmodell<\/a>
\n [LL].[Execution]<\/a>
\n [LL].[Component]<\/a>
\n [LL].[Trace]<\/a>
\n [LL].[Error]<\/a>
\n\n Status der Ausf\u00fchrung des ETL-Prozesses<\/a>
\n\n Prozeduren f\u00fcr die Protokollierung<\/a>
\n [LL].[spInsertTrace]<\/a>
\n [LL].[spUpdateTrace]<\/a>
\n [LL].[spUpdateTraceSuccess]<\/a>
\n\n Exception Handling<\/a>
\n\nBeispiel f\u00fcr die Protokollierung und das Exception-Handling in einem ETL-Prozess<\/a>
\n [T2].[spETLProcess]<\/a>
\n [T2].[spDo\u00adSomething_2_2]<\/a>
\n\nFazit<\/a>
\n\nVerwandte Artikel<\/a>
\n<\/p>\n\n\n\nDas Ergebnis<\/h1>\n\n\n\n
\n
\n
![\"\"](\"https:\/\/sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.trace_-1-1024x195.png\")
![\"\"](\"https:\/\/sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.component-1-1024x93.png\")
![\"\"](\"https:\/\/sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.execution-1-1024x35.png\")
Und nun die Herleitung…<\/h1>\n\n\n\n
\n
Dreistufige Protokollierung<\/h2>\n\n\n\n
[LL].[Execution]<\/h3>\n\n\n\n
[LL].[Component]<\/h3>\n\n\n\n
[LL].[Trace]<\/h3>\n\n\n\n
[LL].[Error]<\/h3>\n\n\n\n
Der Schemaname LL<\/h3>\n\n\n\n
Tabellenbeschreibung, Deklaration und Datenmodell<\/h2>\n\n\n\n
[LL].[Execution]<\/h3>\n\n\n\n
Spalten<\/h4>\n\n\n\n
Spalte<\/th> Beschreibung<\/th><\/tr><\/thead> Id<\/strong><\/td> Die Spalte [Id]<\/strong> ist als IDENTITY <\/strong>deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle.<\/td><\/tr> Process<\/strong><\/td> Die Tabellen f\u00fcr die Protokollierung k\u00f6nnen nicht nur von einem einzigen ETL-Prozess verwendet werden. Damit die Protokolldatens\u00e4tze verschiedener ETL-Prozesse voneinander unterschieden werden k\u00f6nnen, ist in der Spalte [Process]<\/strong> ein eindeutiger Name des ETL-Prozesses zu anzugeben.<\/td><\/tr> Start<\/strong>
End<\/strong><\/td>Diese beiden Spalten geben den Startzeitpunkt und das Ende der Ausf\u00fchrung eines ETL-Prozesses an. Beim Start des ETL-Prozesses wird in der Spalte [Start]<\/strong> der aktuelle Zeitstempel gespeichert. Die Spalte [End]<\/strong> wird beim Start mit einem NULL <\/em>initialisiert. Der letzte Befehl eines ETL-Prozesses muss eine Aktualisierung der Datensatzes mit dem Status vornehmen (Spalten [State]<\/strong> und [Success]<\/strong>). Bei der Aktualisierung wird der aktuelle Zeitstempel in der Spalte [End]<\/strong> gespeichert. Ein NULL <\/em>in der Spalte [End]<\/strong> zeigt entweder an, dass der Prozess noch ausgef\u00fchrt wird, oder dieser mit einer Exception abgebrochen ist.
Zeitangaben werden immer als UTC-Zeit gespeichert.<\/td><\/tr>DeltaStart<\/strong>
DeltaEnd<\/strong><\/td>Diese Spalten legen das Zeitfenster f\u00fcr einen Delta-Load fest. [DeltaStart]<\/strong> legt die untere Zeitgrenze fest und [DeltaEnd]<\/strong> die obere. Das Zeitfenster wird von der Prozedurgespeicherten Prozedur [LL].[spInsertExecution]<\/strong>, die den Protokolldatensatz in diese Tabelle einf\u00fcgt, automatisch ermittelt.<\/td><\/tr> User<\/strong>
Machine<\/strong>
Version<\/strong><\/td>In diesen Spalten ist der Kontext anzugeben, in dem der ETL-Prozess ausgef\u00fchrt wird. Der Benutzer, der in Spalte [User]<\/strong> angegeben ist, ist der Benutzer, unter dem der Prozess ausgef\u00fchrt wird. Dieser ist ma\u00dfgeblich f\u00fcr Berechtigungen an Datenbanken, Tabellen, File-Shares etc. Der Benutzer wird beim Einf\u00fcgen des Protokolldatensatzes \u00fcber die T-SQL Funktion SUSER_SNAME()<\/strong> als Default-Wert ermittelt.
In der Spalte [Machine]<\/strong> wird der Name des Computers gespeichert, auf dem der ETL-Prozess ausgef\u00fchrt wird. Der Name wird beim Einf\u00fcgen des Protokolldatensatzes \u00fcber die T-SQL Funktion HOST_NAME() <\/strong>ebenfalls als Default-Wert ermittelt.
Die Spalte [Version]<\/strong> nimmt die Versionsnummer des ETL-Prozesses und ist gegebenenfalls bei der Fehlersuche nach einem fehlerhaften Deployment eines ETL-Prozesses hilfreich.<\/td><\/tr>State<\/strong><\/td> Die Spalte [State]<\/strong> zeigt den aktuellen Status des ETL-Prozesses an. M\u00f6gliche Werte sin processing<\/em>, warning<\/em>, success<\/em> oder error<\/em>. Initial wird der ETL-Prozess mit [State]<\/strong> gleich processing<\/em> gestartet. Im Erfolgsfall wird [State]<\/strong> mit warning<\/em> oder success<\/em> aktualisiert. Im Fehlerfall wird [State]<\/strong> mit error<\/em> aktualisiert.<\/td><\/tr> Success<\/strong><\/td> Die Spalte [Success] <\/strong>gibt an, ob der ETL-Prozess erfolgreich mit [State]<\/strong> gleich warning<\/em> oder success<\/em> beendet wurde. Initial wird der ETL-Prozess mit [Success]<\/strong> gleich 0<\/em> gestartet. Im Erfolgsfall wird [Success]<\/strong> mit 1<\/em> aktualisiert.<\/td><\/tr> CreatedOn<\/strong>
CreatedBy<\/strong>
ModifiedOn<\/strong>
ModifiedBy<\/strong><\/td>Die Spalten [CreatedOn] <\/strong>und [ModifiedOn]<\/strong> enthalten einen UTC-Zeitstempel. Initial wird [CreatedOn]<\/strong> mit dem aktuellen Zeitstempel \u00fcber einen Default-Wert belegt und [ModifiedOn]<\/strong> mit einem NULL<\/em>. [ModifiedOn] <\/strong>wird bei der Aktualisierung des Status mit dem dann geltenden Zeitstempel aktualisiert. Die gleiche Systematik gilt f\u00fcr die Felder [CreatedBy]<\/strong> und [ModifiedBy].<\/strong> Hier wird der Benutzer gespeichert, unter dem der Prozess ausgef\u00fchrt wird.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Deklaration<\/h4>\n\n\n\n
CREATE TABLE [LL].[Execution] \n(\n [Id] bigint IDENTITY (1, 1) NOT NULL\n ,[Process] nvarchar (max) NOT NULL\n ,[Start] datetime NOT NULL\n ,[End] datetime NULL\n ,[DeltaStart] datetime NULL\n ,[DeltaEnd] datetime NULL\n ,[User] nvarchar(128) NULL\n ,[Machine] nvarchar(128) NULL\n ,[Version] int NULL\n ,[State] nvarchar(128) NULL\n ,[Success] bit NULL\n ,[CreatedOn] datetime\n CONSTRAINT [DF_LL_Execution_CreatedOn] \n DEFAULT (GETUTCDATE()) NOT NULL\n ,[CreatedBy] nvarchar(100)\n CONSTRAINT [DF_LL_Execution_CreatedBy]\n DEFAULT (SUSER_SNAME()) NOT NULL\n ,[ModifiedOn] datetime NULL\n ,[ModifiedBy] nvarchar(100) NULL\n ,CONSTRAINT [PK_LL_Execution] \n PRIMARY KEY CLUSTERED ([Id] ASC)\n);\nGO\n\nCREATE TRIGGER [LL].[TR_LL_Execution_Update]\nON [LL].[Execution]\nFOR UPDATE\nAS\nBEGIN\nSET NOCOUNT ON\n UPDATE [LL].[Execution]\n SET\n [ModifiedOn] = GETUTCDATE()\n ,[ModifiedBy] = SUSER_SNAME()\n FROM\n [LL].[Execution]\n INNER JOIN inserted\n ON\n inserted.[ID] = [LL].[Execution].[ID];\nEND;\nGO<\/pre>\n\n\n\n
[LL].[Component]<\/h3>\n\n\n\n
Spalten<\/h4>\n\n\n\n
Spalte<\/th> Beschreibung<\/th><\/tr><\/thead> Id<\/strong><\/td> Die Spalte [Id]<\/strong> ist als IDENTITY <\/strong>deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle.<\/td><\/tr> ExecutionId<\/strong><\/td> \u00dcber diesen Fremdschl\u00fcssel auf die Tabelle [LL].[Execution]<\/strong> k\u00f6nnen die Protokolldatens\u00e4tze genau einer Ausf\u00fchrung des ETL-Prozesses zugeordnet werden.<\/td><\/tr> Source<\/strong><\/td> Der Text in dieser Spalt gibt an, von welchem Typ die Komponente ist, die den Protokolldatensatz geschrieben hat. Verwendete Werte sind zum Beispiel T-SQL<\/em> oder SSIS<\/em>.<\/td><\/tr> Component<\/strong><\/td> In dieser Spalte wird der Name der Komponente, die den Protokolldatensatz geschrieben hat, festgehalten. Bei Prozeduren ist dieses der Prozedurname, bei anderen Komponenten zum Beispiel der Dateiname der Komponente.<\/td><\/tr> Version<\/strong><\/td> Sofern zu der Komponente eine Versionsnummer verf\u00fcgbar ist, kann und sollte diese in dieser Spalte ausgegeben werden.<\/td><\/tr> Entity<\/strong><\/td> In dieser Spalte ist der Name der Zieltabelle, oder – allgemeiner formuliert – Entit\u00e4t anzugeben, die durch die Komponente bearbeitet wird. Der Name ist frei w\u00e4hlbar. Es empfiehlt sich, den Namen systematisch zu w\u00e4hlen, um eine systematische Auswertung des Protokolls zu erleichtern.<\/td><\/tr> Step<\/strong><\/td> In der Spalte [Step]<\/strong> ist eine kurze Beschreibung der Aufgabe der Komponente anzugeben. Auch diese Beschreibung sollte einer strengen Systematik folgen, um eine leichte Auswertung des Protokolls zu erm\u00f6glichen.<\/td><\/tr> FileId<\/strong><\/td> Die Spalte [FileId] ist f\u00fcr die Verarbeitung von Dateien durch die Komponente vorgesehen. F\u00fcr die Beschreibung der hier vorgestellten Vorgehensweise ist sie nicht von Bedeutung.<\/td><\/tr> Description<\/strong><\/td> In dieser Spalte kann eine weitergehende Beschreibung der Aufgabe der Komponente eingef\u00fcgt werden.<\/td><\/tr> State<\/strong><\/td> Die Spalte [State]<\/strong> zeigt den aktuellen Status der Ausf\u00fchrung der Komponente an. M\u00f6gliche Werte sin processing<\/em>, warning<\/em>, success<\/em> oder error<\/em>. Initial wird die Komponente mit [State]<\/strong> gleich processing<\/em> gestartet. Im Erfolgsfall wird [State]<\/strong> mit warning<\/em> oder success<\/em> aktualisiert. Im Fehlerfall wird [State]<\/strong> mit error<\/em> aktualisiert.<\/td><\/tr> Success<\/strong><\/td> Die Spalte [Success] <\/strong>gibt an, ob die Komponente erfolgreich mit [State]<\/strong> gleich warning<\/em> oder success<\/em> beendet wurde. Initial wird der Komponente mit [Success]<\/strong> gleich 0<\/em> gestartet. Im Erfolgsfall wird [Success]<\/strong> mit 1<\/em> aktualisiert.<\/td><\/tr> CreatedOn<\/strong>
CreatedBy<\/strong>
ModifiedOn<\/strong>
ModifiedBy<\/strong><\/td>Die Spalten [CreatedOn] <\/strong>und [ModifiedOn]<\/strong> enthalten einen UTC-Zeitstempel. Initial wird [CreatedOn]<\/strong> mit dem aktuellen Zeitstempel \u00fcber einen Default-Wert belegt und [ModifiedOn]<\/strong> mit einem NULL<\/em>. [ModifiedOn] <\/strong>wird bei der Aktualisierung des Status mit dem dann geltenden Zeitstempel aktualisiert. Die gleiche Systematik gilt f\u00fcr die Felder [CreatedBy]<\/strong> und [ModifiedBy].<\/strong> Hier wird der Benutzer gespeichert, unter dem der Prozess ausgef\u00fchrt wird.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Deklaration<\/h4>\n\n\n\n
CREATE TABLE [LL].[Component] \n(\n [Id] bigint IDENTITY (1, 1) NOT NULL\n ,[ExecutionId] bigint NOT NULL\n ,[Source] nvarchar (5) NOT NULL\n ,[Component] nvarchar (128) NOT NULL\n ,[Version] int NULL\n ,[Entity] nvarchar (128) NOT NULL\n ,[Step] nvarchar (MAX) NOT NULL\n ,[Description] nvarchar (MAX) NULL\n ,[FileId] bigint NULL\n ,[State] nvarchar (128) NULL\n ,[Success] bit NULL\n ,[CreatedOn] datetime\n CONSTRAINT [DF_LL_Execution_CreatedOn] \n DEFAULT (GETUTCDATE()) NOT NULL\n ,[CreatedBy] nvarchar (100) \n CONSTRAINT [DF_LL_Execution_CreatedBy] \n DEFAULT (SUSER_SNAME()) NOT NULL\n ,[ModifiedOn] datetime NULL\n ,[ModifiedBy] nvarchar (100) NULL\n ,CONSTRAINT [PK_LL_Component] \n PRIMARY KEY CLUSTERED ([Id] ASC),\n ,CONSTRAINT [FK_LL_Component_ExecutionId] \n FOREIGN KEY ([ExecutionId]) \n REFERENCES [LL].[Execution] ([Id])\n);\nGO\n\nCREATE TRIGGER [LL].[TR_LL_Component_Update]\nON [LL].[Component]\nFOR UPDATE\nAS\nBEGIN\nSET NOCOUNT ON\n\n UPDATE [LL].[Component]\n SET\n [ModifiedOn] = GETUTCDATE()\n ,[ModifiedBy] = SUSER_SNAME()\n FROM\n [LL].[Component]\n INNER JOIN inserted\n ON\n inserted.[ID] = [LL].[Component].[ID];\nEND;\nGO<\/pre>\n\n\n\n
[LL].[Trace]<\/h3>\n\n\n\n
Spalten<\/h4>\n\n\n\n
Spalte<\/th> Beschreibung<\/th><\/tr><\/thead> Id<\/strong><\/td> Die Spalte [Id]<\/strong> ist als IDENTITY <\/strong>deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle.<\/td><\/tr> ExecutionId<\/strong><\/td> \u00dcber diesen Fremdschl\u00fcssel auf die Tabelle [LL].[Execution]<\/strong> k\u00f6nnen die Protokolldatens\u00e4tze genau einer Ausf\u00fchrung des ETL-Prozesses zugeordnet werden.<\/td><\/tr>