Customised Data Validation System for Data Count and Quality check in sql server 2019

Question

I am trying to design Data Validation system using SQL SERVER.

This Question is more with Design Challenge.

Scenario:- Data Reconcilation between Source, RAW, Staging,validation and Target . Data Flows from Source to RAW, Staging,validation and Target using ETL (SSIS) tool. Each stage perform predefined task like cleasing and Aggregation.... etc I want to design a Data Validation system which gives me clear picture for my data how it looks after each stage. There should be 1 table for each source, as schema for each source is different.

Seeking Expertise Suggestion. What should be my design approach for building solution which can tell data story, correctness and completeness between Source, RAW, Staging,validation and Target. Moreover how to accomdate ETL Script which is altering data between each stage., so if data got change from 1 point to another I can tell it happen due to ETL Script....Seems really challenging.....

May Anyone already develop this kind of system, please help me with your expertise knowledge. Thanks

Answer 1

Hi @Uma,

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

You can configure the scope of logging that occurs during a package execution on the Integration Services server. For more information, see Enable Logging for Package Execution on the SSIS Server.

More details you may refer to: Integration Services (SSIS) Logging

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.