I have a requirement like, we will receive multiple csv files in a shared path and those files needs to be processed to the corresponding tables.
Say for eg: if Employee file has 3 files like employee_2022_08_08_11_11 / employee_2022_08_07_12_00 / employee_2022_08_08_16_07 i need to process these three files to my employee table and say for Department Department_2022_08_08_12_08 to Department table in SQL Server.
Previously I had a reference table where the file name has stored as
So we were processing the file names by seeing the reference table, but the requirement has changed , need to process whatever file is there in the path irrespective of dates, after processing it will be moved to another folder(this part is working fine). But how to take multiple files and process without altering the existing logic with the reference table.
Bulk insert option has been used to load the data to read from Shared path in SSIS package.
Is there any approach or any suggestions.