Warm welcome friends, today in this session we will discuss how to load multiple source files in SSIS or loading up one or more files using FOREACH LOOP Container in SSIS.
In our previous article we saw how to implement ERROR HANDLING IN SSIS. If you are looking to earn #MSBI step by step then this is best place to find all complete source of #MSBI.
Little About us : We are QUESTPOND an e-learning firm since 15 yrs teaching all Microsoft products like Sharepoint, Design Pattern, MSBI, MVC, WCF, WPF, AngularJS, Silverlight, LINQ, Software Architecture, .NET Core, C# and many more.
We do conduct one-one training and also online training for working professionals. If you want to level up your skills or want to learn new technology then feel-free to get back to us on the below mention contact details.
Self Learning : We do have complete DVD pack of videos as a self-learning material check our Pricing Page to know more.
Any doubts feel free to get back to us. We give complete support.
Now coming to back to article. Till now in our previous articles we seen ETL process using single source file or examples using single source file. What happens if there are multiple source files and to be loaded at a same time. This scenario will definately comes when you start working with #SSIS. So it is very important to understand of loading multiple sources files in #SSIS.
In order to load multiple source files which can done at control flow tab so to implement that we need use FOR LOOP Container or FOREACH loop container.
The Foreach Loop container defines a repeating control flow in a ssis package. In simple words FOREACH LOOP is available in a ssis toolbox of control flow tab. It is widely used to do multiple task like let's say if you have bulk source files and at a same time if you want to load that files then we can do that by using FOREACH loop.
ETL process activities can be done in "Data Flow" Tab and Non-ETL process activites can be done in "Control Flow" Tab.
There is also one more conatiner i.e. FOR LOOP but to use FOR LOOP we should know the number of source files with file-name. FOR LOOP loops through fixed count
When there are collection of source files in a folder then it is better to use FOREACH LOOP CONTAINER because FOREACH loop loops through items in floder or recordset.
When there is no fixed count of files or when we dont no number of recordset it is better to implement FOREACH LOOP. In this scenario we want to loop through a folder with number source files so we will use FOREACH LOOP CONTAINER.
In this example we will implement FOREACH LOOP CONTAINER in a CONTROL TAB on a bulk folder which will have multiple source files and that with help of FOREACH loop we will iterate each file and pass that to DATA FLOR TAB for ETL process. NOTE : If you want to know more about ETL process in SSIS kindly check this article link.
FOREACH LOOP Container is only available at Control Tab in SSIS tool box.
So we will be implementing FOREACH LOOP Container at control flow stage.
Step 1
Let's create multiple CSV files for Customer records as shown below. FOREACH LOOP container can be used if there are multiple items to iterate. So that's why we will first create multiple CSV file as our source data.
Step 2
In order to do this example first we need to create a ETL process for a single file. So create a ETL process for single CSV file in order to know more about ETL process implementation click here and also check our previous article i.e. SSIS error handling it is also ETL process but with an error handling mechanism and here in this session we will be using this as an example.
So create an ETL process for a single file and proceed to next step.
Step 3
Once after ETL process is completed now to make it easy we will save both our CSV files to a folder called "BULKFILES".
Create a new folder called "BULKFILES" and move both CSV files here.
Step 4
In this step on "Control Flow" tab we will create a variable why because if you see on "Data Flow" tab our source i.e. in Flat file Connection we have physical file name i.e. "cust.txt" which is one single file name but since we are working with multiple files so that file path should be dynamic so in order to make it dynamic we will create a variable in "CONTROL FLOW" tab and pass that full file name through this variable name to "Data Flow" using foreach loop dynamically.
At this moment let's create a variable .
In order to create a variable goto -> Control Flow -> right click -> Add Variable as shown in below image.
Add variable and give the suitable name and since we are passing file name so our variable data-type should be "String" as shown in below image.
This above variable will be used to pass to "Data Flow" tab which will be containing "FullFilePath".
Step 5
In this step we will add a FOREACH LOOP container so in order add go to -> SSIS toolbox -> FOREACH LOOP container.
Now drag and drop data flow task container inside the FOREACH LOOP container as shsown in below image.
Step 6
In this step we will configure FOREACH LOOP container. so just select container and right click -> edit.
From left-hand side menu select collection and from enumerator select "FOREACH FILE Enumerator".
Go to below in the folder section folder path i.e. "Bulk folder" which we have created earlier and below that give extension as ".txt" since our data is in text format as shown in below image.
Do not close that modal now we have map our variable name to "FOREACH FILE Enumerator" to handle "File path" and same to pass to "Data Flow" tab. So just below collection tab select Variable mapping from left-hand side menu and choose variable name which we have created earlier i.e. "VarFullFilePath" as shown in below image.
By this what will happen that everytime "FOREACH FILE Enumerator" loops and get a file it will assign that file path to this variable "VarFullFilePath".
Step 7
After configuring variable at control flow tab now we should also confirgure at Data Flow table at source end because it is where file name is needed for ETL process.
So goto -? Data Flow tab and -> Connection Manage -> right click on CSVConnectionManager -> Properties as shown in below image.
In the properties window if you see current "ConnectionString" it consist of single file path that we want to make it dynamic by assigning "VarFullFilePath".
So to do that in the same property window there "Expression" menu -> configure -> click. In the property select "ConnectionString" and click on expression select variable "VarFullFilePath" and drag it to expression and click on OK as shown in below image.
Step 8
This is final step just run this project to see the output.
As you see we have successfully executed the project and we successfully loaded data from both sources and same update to destination data source.
Thank you, for reading this article if you have any doubts or query regarding "Loading Multiple Files / Implementing FOREACH Loop in SSIS" feel free to know us. If you like this article dont forgot to thumbs up on your social accounts.
Order only MSBI self-study learning video materials which are available with customized package costing 999 INR/15$. Click this link here to see details and order it.
