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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.