Hi,
I need to design a system which will process hundreds of source files (different format fixed width and delimited files) and convert to one target files. There should be two interfaces one is command line other web user interface? Command line interface to run the transform job though batch and web user interface to define the format of source file and mapping details of source file to target file i.e. one time job for each source file.
All the source files are fixed width or delimited files.
What is the correct approach? Should I create one stage table for each source file to the stage data? Should I create one stage table on runtime when user will define of layout (Most are fixed width mainframe files) of source file? Should I create only one generic table, containing around 100 columns all varchar type?
I am looking for the best approach to design the system. Performance is very critical for this app. There are hundreds of files and we need to transform all the files daily within certain time.
Thanks in Advance Akshay