Of course, we also want to know whether the process as a whole, a procedure or even a specific SQL statement was executed successfully.<\/p>\n\n\n\n
The procedure is actually straightforward: At the beginning of each action, a log data record is written and after the action is completed, it is updated with either the status success or failure and, if necessary, other helpful information. That’s it! But it can still be a little more…<\/p>\n\n\n\n\n\n\n\n
Content<\/h1>\n\n\n\n
\nThe Result<\/a> Even if the creation of a protocol itself is not or should not be a big deal, there is still a lot to write about. But let’s start with the result and put the cart before the horse. The process logging presented here is three-tier and uses the following log tables:<\/p>\n\n\n\n In these tables, an ETL process and the associated components and work steps are logged with increasing granularity – read from top to bottom. The ETL process, the components and the individual action are logged with exactly one row.<\/p>\n\n\n\n Of course, the logging of errors is also closely linked to the creation of the logging of the process. The logging approach presented here uses the following table for logging errors:<\/p>\n\n\n\n The following images show the result of the three-stage logging of a simple, compact but complete ETL process.<\/p>\n\n\n\n In the [LL].[Trace]<\/strong> table, each action is recorded, among other things, with the name of the procedure, the target entity that is processed by the procedure, a short description of what was specifically done and other information, such as the number of affected Records and duration of execution are logged.<\/p>\n\n\n\n The [LL].[Component]<\/strong> table logs the calls to procedures and SSIS packages or – generally speaking – components. Here too, a short description of the component’s task, the target entity and the duration of execution are logged.<\/p>\n\n\n\n At the top level, each execution of the ETL process is logged in the [LL].[Execution]<\/strong> table with exactly one row.<\/p>\n\n\n\n All process logging tables contain two columns [State]<\/strong> and [Success]<\/strong>, which store the success or failure of the execution of an action, component, or ETL process.<\/p>\n\n\n\n After the brief introduction of the result, the following aspects of the procedure need to be clarified:<\/p>\n\n\n\n The procedure involves logging an ETL process in the three tables [LL].[Execution]<\/strong>, [LL].[Component]<\/strong> and [LL].[Trace]<\/strong>. Each of the tables is intended for logging specific artifacts. This section introduces the use of each table, its columns, and the code to create it.<\/p>\n\n\n\n This table records the execution of an ETL process with exactly one record. An ETL process always has an entry function. This can be a stored procedure, an SSIS package, a Talend job, or a SQL Server Agent<\/em> job. At the beginning of execution of the entry function, a log record is inserted into this table. After all tasks have been successfully processed, this data record is updated with the status success<\/em>, or in the event of an error with the status error<\/em>. This table therefore contains an overview of all executions of the ETL process and provides information such as the status and duration of the execution.<\/p>\n\n\n\n In this table, the execution of a component is logged with exactly one row. A component can be a stored procedure, an SSIS package, a Talend job. A component is characterized by the fact that it controls and executes one or more data manipulations or other components. As with the table [LL].[Execution]<\/strong>, a log entry is inserted into this table at the beginning of execution and updated with the status success <\/em>or error <\/em>upon completion. This table therefore contains a list of the components executed for each execution of the ETL process and provides information such as the status and duration of the execution for each component.<\/p>\n\n\n\n The name of this table already indicates that the table is intended for a detailed logging of actions of the ETL process. Which actions are logged is a design decision by the developer. However, it is recommended to log at least every INSERT, UPDATE and DELETE statement with its own log entry. In addition to the status fields mentioned above, the table also contains a column that provides the number of affected rows of a data manipulation task, thus giving the developer the opportunity to assess whether the statements did exactly what was expected.<\/p>\n\n\n\n Errors that were detected in an ETL process must of course be logged. A distinction must be made between the logging of exceptions and data errors. The structure of thi<\/p>\n\n\n\n s table is designed for the logging of data errors and contains columns in which each data errors can be logged completely, readably and evaluably. This article focuses on logging an ETL process rather than logging data errors. The logging of the process is closely linked to explicit exception handling. A separate section is dedicated to exception handling and exception logging.<\/p>\n\n\n\n The schema name LL<\/strong> stands for Logging Layer<\/em>. This schema stores all the log tables and the stored procedures used for logging.<\/p>\n\n\n\n The following sections describe the process logging tables and the error logging table. Finally, there is a diagram with the data model for these tables.<\/p>\n<\/div><\/div>\n\n\n\n The table contains the following columns:<\/p>\n\n\n\n The table is created using the following statement:<\/p>\n\n\n\n The table contains the following columns:<\/p>\n\n\n\n The table is created using the following statement:<\/p>\n\n\n\n The table contains the following columns:<\/p>\n\n\n\n
\nLet’s start…<\/a>
\n\n Three-tier logging<\/a>
\n [LL].[Execution]<\/a>
\n [LL].[Component]<\/a>
\n [LL].[Trace]<\/a>
\n Schema LL<\/a>
\n\n Table descriptions, declaration and data model<\/a>
\n [LL].[Execution]<\/a>
\n [LL].[Component]<\/a>
\n [LL].[Trace]<\/a>
\n [LL].[Error]<\/a>
\n\n ETL process execution status<\/a>
\n\n Stored procedures for logging<\/a>
\n [LL].[spInsertTrace]<\/a>
\n [LL].[spUpdateTrace]<\/a>
\n [LL].[spUpdateTraceSuccess]<\/a>
\n\n ExceptionHandling<\/a>
\n\nExample of logging and exception handling in an ETL process<\/a>
\n [T2].[spETLProcess]<\/a>
\n [T2].[spDo\u00adSomething_2_2]<\/a>
\n\nSummary<\/a>
\n\nRelated Posts<\/a>
\n\nDownload<\/a>
\n<\/p>\n\n\n\nThe result<\/h1>\n\n\n\n
\n
\n
![\"\"](\"https:\/\/staging.sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.trace_-1-1024x195.png\")
![\"\"](\"https:\/\/staging.sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.component-1-1024x93.png\")
![\"\"](\"https:\/\/staging.sql.marcus-belz.de\/wp-content\/uploads\/2024\/02\/0304.example.execution-1-1024x35.png\")
Let’s start\u2026<\/h1>\n\n\n\n
\n
Three-tier logging<\/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
Schema LL<\/h3>\n\n\n\n
Table descriptions, declaration and data model<\/h2>\n\n\n\n
[LL].[Execution]<\/h3>\n\n\n\n
Columns<\/h4>\n\n\n\n
Column<\/th> Description<\/th><\/tr><\/thead> Id<\/strong><\/td> The [Id]<\/strong> column is declared as IDENTITY <\/strong>and identifies a log record in this table.<\/td><\/tr> Process<\/strong><\/td> The logging tables cannot be used by just one ETL process. So that the log data records of different ETL processes can be distinguished from one another, a unique name of the ETL process must be specified in the [Process]<\/strong> column.<\/td><\/tr> Start<\/strong>
End<\/strong><\/td>These two columns indicate the start time and the end of the execution of an ETL process. When the ETL process starts, the current timestamp is saved in the [Start]<\/strong> column. The [End]<\/strong> column is initialized with a NULL <\/em>at startup. The last command of an ETL process must update the state of the log entry ([State]<\/strong> and [Success]<\/strong> columns). When updated, the current timestamp is saved in the [End]<\/strong> column. A NULL <\/em>in the [End] <\/strong>column indicates either that the process is still running or that it has aborted with an exception. Times are always stored as UTC time.<\/td><\/tr> DeltaStart<\/strong>
DeltaEnd<\/strong><\/td>These columns determine the time window for a delta load. [DeltaStart]<\/strong> sets the lower time limit and [DeltaEnd]<\/strong> sets the upper one. The time window is automatically determined by the stored procedure [LL].[spInsertExecution]<\/strong>, which inserts the log entry into this table.<\/td><\/tr> User<\/strong>
Machine<\/strong>
Version<\/strong><\/td>These columns must indicate the context in which the ETL process is executed. The user specified in the [User] <\/strong>column is the user under which the process is executed. This is crucial for authorizations to databases, tables, file shares, etc. The user is determined by automatically using the T-SQL function SUSER_SNAME() <\/strong>when inserting the log entry.
The [Machine] <\/strong>column stores the name of the computer on which the ETL process is running. The name is also determined automatically when the log entry is inserted using the T-SQL function HOST_NAME()<\/strong>.
The [Version]<\/strong> column takes the version number of the ETL process and may be helpful for troubleshooting after an incorrect deployment of an ETL process.<\/td><\/tr>State<\/strong><\/td> The [State]<\/strong> column displays the current status of the ETL process. Possible values include processing<\/em>, warning<\/em>, success <\/em>or error<\/em>. Initially the ETL process is started with [State]<\/strong> = processing<\/em>. If successful, [State]<\/strong> is updated with warning <\/em>or success<\/em>. In case of an error, [State]<\/strong> is updated with error<\/em>.<\/td><\/tr> Success<\/strong><\/td> The [Success]<\/strong> column indicates whether the ETL process completed successfully with [State]<\/strong> = warning <\/em>or success<\/em>. Initially the ETL process is started with [Success]<\/strong> = 0<\/em>. If successful, [Success]<\/strong> is updated with 1<\/em>.<\/td><\/tr> CreatedOn<\/strong>
CreatedBy<\/strong>
ModifiedOn<\/strong>
ModifiedBy<\/strong><\/td>The [CreatedOn]<\/strong> and [ModifiedOn]<\/strong> columns contain a UTC timestamp. Initially, [CreatedOn]<\/strong> is assigned the current timestamp via a default value and [ModifiedOn]<\/strong> is assigned a NULL. [ModifiedOn]<\/strong> is updated with the current timestamp when the status is updated. The same system applies to the fields [CreatedBy] <\/strong>and [ModifiedBy]<\/strong>. The user under which the process is executed is stored here.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Declaration<\/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
Columns<\/h4>\n\n\n\n
Column<\/th> Descritpion<\/th><\/tr><\/thead> Id<\/strong><\/td> The [Id]<\/strong> column is declared as IDENTITY <\/strong>and identifies a log record in this table.<\/td><\/tr> ExecutionId<\/strong><\/td> Using this foreign key on the table [LL].[Execution]<\/strong>, log entries that are related to a specific execution of the ETL process can be identified.<\/td><\/tr> Source<\/strong><\/td> The text in this column indicates the type of component that wrote the log record. Values used are, for example, T-SQL<\/em> or SSIS<\/em>.<\/td><\/tr> Component<\/strong><\/td> This column holds the name of the component that wrote the entry. For procedures, this is the procedure name, for other components, for example, the file name of the component.<\/td><\/tr> Version<\/strong><\/td> If a version number is available for the component, it can and should be logged in this column.<\/td><\/tr> Entity<\/strong><\/td> The name of the target table or – to put it more generally – the entity that is processed by the component must be specified in this column. The name can be freely chosen. It is recommended to choose the name systematically in order to facilitate a systematic evaluation of the protocol.<\/td><\/tr> Step<\/strong><\/td> A short description of the component’s task must be provided in the column [Step]<\/strong>. This description should also follow a strict system to enable easy evaluation of the protocol.<\/td><\/tr> FileId<\/strong><\/td> The [FileId]<\/strong> column is used for the processing of files by the component. It is not important for describing the procedure presented here.<\/td><\/tr> Description<\/strong><\/td> An additional description of the component’s task can be inserted in this column.<\/td><\/tr> State<\/strong><\/td> The [State]<\/strong> column displays the current status of the component’s execution. Possible values include processing<\/em>, warning<\/em>, success<\/em> or error<\/em>. Initially the component is started with [State]<\/strong> equal to processing<\/em>. If successful, [State]<\/strong> is updated with warning <\/em>or success<\/em>. In case of an error, [State]<\/strong> is updated with error<\/em>.<\/td><\/tr> Success<\/strong><\/td> The column [Success]<\/strong> indicates whether the component ended successfully with [State]<\/strong> equal to warning <\/em>or success<\/em>. Initially the component is started with [Success]<\/strong> equal to 0<\/em>. If successful, [Success]<\/strong> is updated with 1<\/em>.<\/td><\/tr> CreatedOn<\/strong>
CreatedBy<\/strong>
ModifiedOn<\/strong>
ModifiedBy<\/strong><\/td>See table [LL].[Execution]<\/strong>.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Declaration<\/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
Columns<\/h4>\n\n\n\n
Column<\/th> Description<\/th><\/tr><\/thead> Id<\/strong><\/td> The [Id]<\/strong> column is declared as IDENTITY <\/strong>and identifies a log record in this table.<\/td><\/tr> ExecutionId<\/strong><\/td> Using this foreign key to table [LL].[Execution]<\/strong>, log entries that are related to a specific execution of the ETL process can be identified.<\/td><\/tr> ComponentId<\/strong><\/td> Using this foreign key to table [LL].[Component]<\/strong>, log entries that are related to a specific execution of a component can be identified.<\/td><\/tr> Source<\/strong><\/td> The text in this column indicates the type of component that wrote the log record. Values used are, for example,