Design Data Validation System in SQL SERVER

Question

I have 5 source system which is sending data in .csv format and it is getting loaded to SQL SERVER System.

Can anyone please suggest the design approach for Data Validation.

basically a reconciliation system, which allow to perform data validation in sql server .

Tech Stack :

C#, sql server, asp.net (wcf)....

Thank You so much.

Answer 1

Hi @Rosalina5,

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are missing completely.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

SQL Server Integration Services (SSIS) has two tasks for your needs:

• XML Task, Operation Validate, to validate XML file against an XSD.
• XML Source Adapter, to ingest data.

Answer 2

I opt for utilizing an automated flow to stage data, validate the ETL process, and populate the production table.

And suggest using SSIS and SQL Agent Jobs.