Tuesday, May 6, 2008

How to simplify flat file exchange process

The aim of this blog is to present and explain solution for flat file validation that I had build while working as DBA. This is literally an application that can separate rows of your files into ones that pass you validation criteria and ones that don't.

When you will want to use it?
There is one main that I had in mind while writing the code:
There is one DB with data and many external sources that supply data mainly via text files. Those text files should be compliant with some rules for successful import.

You aim is to be confident in data quality (technical side at list) once you've provided your data supplier with those rules. What happens in reality? That does not work. They have document that describes data fields and what data should be there but too often something goes wrong and have to build double check process anyway. Then ask for data resupply if something went wrong.

Now how can you guaranty that you data provider ensures all rules in the document you gave him? This document should be a Flat File Schema (FFS) that can be applied to data files through easily accessible (free license + internet download) application.

Application that can allow you to build Schema (XML document) through GUI;
XML Schema for FFS that is used for syntax validation;