I have a situation where I have over 500 different clients who will be sending files either in excel spreadsheet or csv text files. Our company is pretty small and the files that we receive are sent by HR department. There’s no file naming convention and file format is not consistent. Each client will be sending files to their own folder. Is it possible to write an ETL process perhaps in SSIS to load the file in a staging table as they come in then map and load it to production table based on column names? The ordinal position of the column will not be same each time and also will differ from clients to clients. Would it be possible to write one ssis package rather than over 500 packages to achieve this? I would appreciate if anyone can provide any suggestions to me. Thanks.
Probably best to use PS to either separate them into designated folders or convert them into one format or the other. If the column names are consistent you could use c# script task to dynamically load the files. The easiest route though would be to set up a template format for the clients to use in the future to standardise this process. Hope this helps N blog for c# script (