SSIS - Loop and get all the file data using Foreach
It is easy to map all the files in a specific disk folder and fetch the data for further processing using SSIS.
SSIS is having Foreach Loop container, drag this task tool from the tool box to the work area. For the demonstration of the usage of this task, I am going to pull data from multiple CSV files copied into folder in my system M:\Jithesh.
Map the folder name in Foreach container properties and decide what kind o files we need to use it for data collection. it can find any type of files inside this folder or we can restrict based on our requirement. here I would like to consider only CSV files.
As you seen in the Fig 0.2, provide the folder path and the type file in Files section, here as I mentioned *.csv picks all the CSV files in this path for the processing.
we can retrieve the file name and pass to the task inside the Foreach loop in different ways, Fully qualified pick the name and the path of the file so finally looks like C:\Jithesh\a.csv
We need to map the user variable to retrieve the file name from this task.
Now pass this variable (filename) to the ConnectionString property of the input file as a expression to fetch the data dynamically!!..
Foreach Loop continue till the last file and each time it retrieves the current file name and pass to the variable and the input file path (source file) changes accordingly.
SSIS is having Foreach Loop container, drag this task tool from the tool box to the work area. For the demonstration of the usage of this task, I am going to pull data from multiple CSV files copied into folder in my system M:\Jithesh.
 |
| Fig 0.1 |
Map the folder name in Foreach container properties and decide what kind o files we need to use it for data collection. it can find any type of files inside this folder or we can restrict based on our requirement. here I would like to consider only CSV files.
 |
| Fig 0.2 |
As you seen in the Fig 0.2, provide the folder path and the type file in Files section, here as I mentioned *.csv picks all the CSV files in this path for the processing.
we can retrieve the file name and pass to the task inside the Foreach loop in different ways, Fully qualified pick the name and the path of the file so finally looks like C:\Jithesh\a.csv
We need to map the user variable to retrieve the file name from this task.
 |
| Fig 0.3 |
Now pass this variable (filename) to the ConnectionString property of the input file as a expression to fetch the data dynamically!!..
Foreach Loop continue till the last file and each time it retrieves the current file name and pass to the variable and the input file path (source file) changes accordingly.
Comments
Post a Comment