Building an external ETL audit and audit reporting

In this recipe, we will implement the external user-built ETL audit mechanism. Our ETL audit will include information about the start and stop times of the workflows running within the job, their statuses, names, and information about which job they belong to.

Getting ready…

We need to create an ETL audit table in our database where we will store the audit results.

Connect to the STAGE database using the SQL Server Management Studio and execute the following statement to create the ETL audit table:

create table dbo.etl_audit (
  job_run_id integer, 
  workflow_status varchar(50),
  job_name varchar(255),
  start_dt datetime,
  end_dt datetime,
  process_name varchar(255)

How to do it…

First, we need to choose ...

Get SAP Data Services 4.x Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.