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

Uma 426 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,898 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 Reputation points

    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


    Zoe Hui

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

    0 comments No comments