I am working on one of the projects where we are asked to look into the options of Metadata driven data acquisition from source systems. Essentially it means that we should be able to achieve following
- Maintain metadata level information for each source system (currently SQL Server, Oracle, MS Access and flat files. May include more sources by time). This will include source system details, tables and extraction logic (full vs. delta extraction)
- To develop system which should be able to generate scripts and pull/load data on run-time based on the metadata information. The program should be able to create extraction scripts at run-time and than execute them to load the data in staging area.
- This needs to be followed by exception handling and error reporting.
This will ideally give us the leverage to create an environment where we can add source systems with minimal effort required. I am aware that SQL Server allows certain metadata based development but is there a similar functionality availabe in Teradata (Data Warehouse builder or TPT maybe) ?