Data upload from multiple files formats into one destination table using SSIS.
Most of the time in SSIS, the data upload process is designed for the same type of file's format, but how to manage the data upload process if there are multiple file's formats available in a folder. In this situation we need to prepare a SSIS logic which will help to design the dynamic process to take care of different file's formats.
Let's assume a scenario in which there are multiple data files of different formats which contains excel, csv and text files. There can be multiple approaches to implement this scenario
The above scenario can be achieved by the following approaches.
Approach 1 - Keep multiple data flow tasks inside the single For each loop container.
Maintain multiple Data Flow Tasks inside For each loop container and each data flow task will handle one particular file format.
Example - There will be three type of data sources for excel, csv and text files.