Tuesday, 9 February 2016

SSIS - How To Load Multiple Files ( .txt or .csv or .excel ) To a Table in SQL server ?

Overview
Data upload from multiple files formats into one destination table using SSIS.

Problem
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

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


In the above screen shot there are three data flow tasks are taken to handle with three kind of files like as excel, csv and text file. Task flow to the appropriate data flow task will be handled based on the expression written into the Precedence constraints.

Approach 2 - Maintain only one Data Flow Task and create multiple data sources based on the available file types. 
Example - There will be three type of data sources for excel, csv and text files.

Approach 3 - There will be a separate For Each Loop container for each format and will be a parallel execution on For each loops. 
Example - all the excel files will be processed through a separate for each loop, in the same manner all the csv files will be processed through a separate for each loop and all the text files will be processed through a separate for each loop. 

No comments:

Post a Comment