I have 20 plus flat files flowing into App server via FTP to load into BI on MONTHLY basis.
+Now I have to validate every flat file with customer master. If a customer match is not found error log need to be generated and later error out files need to be identified for further correction.+
Please give me your inputs in designing this entire process. Below are few specific questions I need your suggestion for.
1.What Flat File naming convention I have to follow, so that managing these files will be feasible down the road. Do I need separate naming standard for corrected files?.
2.What kind of Folder structure on the Application server is needed to store these files initially and how they need to stored after the processing is done with validation program. I mean do I need to use separate folders for correct and error files?
3.On what criteria I should archive these files? Do I need to archive both Correct and error files if so usually how long? (Files are transmitted on monthly basis).
4.Standard on structure of error log. Basically I want monthly error report to see the error files and related error records. Can I store the error log in to a transparent table? If yes what will be the structure of the transparent table?
5.Can I merge all the correct files in to a single file so that loading a consolidated version will be easy? If file size grows after certain memory limit can I split in to multiple files?
Please share your thoughts.