Flat File : Standards

[Former Member]

Scenario:

I have 20 plus flat files flowing into App server via FTP to load into BI on MONTHLY basis.

*Business need😘

+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.

[former_member156446]

These are all the question which can be answered by your Tech lead... most of the answers are Client specific. Whatz std for u might not be std. for me.

[Former Member]

J@Y :I understand that.. Just give your shot

[former_member156446]

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?.

> Have your ODS name _date1 _date2 _date3 etc...

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?

> just have _error.txt for error file

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).

> Once processed move the file to diff folder, or rename the file _archive.txt

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?

>that is up to you.. I would suggest to have all the records along with a extra column with the message at the end, so that they can write a select * on the same Transparent table and get a re-processing file.

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?

> better split initially, and follow on single process always.