Hey MSBI friends, welcome to #MSBI step by step tutorial series this is Gurunatha Dogi a faculty in Questpond having more than 8 yrs of experience in #programming and #server tools.
Today, in this article we will learn how to implement the for loop container in SSIS. Before starting the article session let me just tell you about us : We are #Questpond - A leading training institute in Technology. We teach everything related to Microsoft.NET, C#, OOPS, MSBI, Sharepoint, Java, Testing, .NET Core, MVC, SQL and so on. We are active bunch of group in technological world to make learning easy step by step.
Here you will find all the resources realated to #MSBI. If you want to know more about our teaching and self-learning materials feel free to contact us on below mention details.
In this article we will implement For Loop container in SSIS. Before reading this article just request you to read my previous article i.e. FOREACH LOOP CONTAINER because this article i will be using example of my previous article. So kindly read it once before get starting of this article.
For example let me exaplain you the scenario : Here we have two source folder one for "GoldCustomer" and another one for "NormalCustomer" and inside these folder there are multiple data source files combined with "GoldCustomer" and "NormalCustomer" source files. So here what we want to do that we want to loop both the folders but we want to consider only "GoldCustomer" and "NormalCustomer" naming multiple source files for ETL process and rest other non-related source files we don't to take it.
If we take foreach loop container for both folders then it could only loop source files inside that one folder only with a single FileSpec (GoldCustomer *). It cannot able to jump back to root folder to loop again another folder with a different FileSpec (NormalCustomer *). For this only we considered "FOR LOOP" to pass folder path and FileSpec i.e. (GoldCustomer *) and (NormalCustomer *).
Because of this reason we have used FOR LOOP container to loop both folders and to get their source files path and naming Goldcustomer and Normalcustomer.
As you can see from above images since there are multiple source files naming "GoldCustomer * .txt" and "NormalCustomer * .txt" inside "GoldCustomer" and "NormalCustomer" folders so to loop that files we need to use FOREACH loop container here we cannot use FOR LOOP why because naming is not in sequence format or names are in different format with a starting "GoldCustomer * .txt" and "NormalCustomer * .txt" so for that we have to FOREACH LOOP inside the FORLOOP container.
Then responsibility of FOREACH LOOP container to pass each source file name for ETL process. If you want to know more about ETL process then click to read here.
Responsibility of "FOR LOOP" to pass the folder path and FileSpec i.e. "goldcustomer *" and "normalcustomer *" respectively. and responsibility of "FOREACH LOOP" to get that "FolderPath and FileSpec from "FOR LOOP" and using that FileSpec to loop each file inside that Folderpath starting with "goldcustomer *" and "normalcustomer *" then passing same file name for ETL process. Hope you have understood scenario so let's start our example step by step.
Step 1
Prepare a same example which we did it our previous article i.e. FOREACH LOOP to know more visit here.
Step 2
Once you have created the same example as we did it in our previous article.
Here in this step we will create variable in "Control Flow". Since we have two folders along with their file name so for this we need to create two variables to store two folder-path and two variables to store file-name "goldcustomer *" and "normalcustomer *" respectively. We will add two more variables i.e. FileName and FolderPath to pass values to ForEach Loop container.
Since we are using "FOR LOOP" container it requires two variables i.e. once for Initialization and another for total number of loops to Evaluate Expression variable. So let's add two more variable and name it as IncrementValue and TotalCount.
Assign values like
Step 3
Now just drap and drop FOR LOOP container to control tab and drag complete foreach loop inside the for loop container as shown in below image.
Step 4
Time to configure For Loop so select For loop container and right click on it -> Edit -> For Loop Properties.
Here we need to specify InitExpression, EvalExpression and AssignExpression so specify it as shown in below image. InitExpression will be IncrementValue (Initial Value) and EvalExpression (Evaluating expression i.e. Initial Value less than or equal to TotalCount) and finally AssignExpression (To increment Initial Value everytime it loops).
What we are saying above is to loop for loop until Evaluating expression false it means here it will loops two times because our starting intial value is 1 and loop first round it increments to 2 and it cannot loop third round coz Evaluating expression returns false (3 <= 2).
Step 5
In this step we need to set up two variables i.e. FolderPath and FileName.
Set up FolderPath variable so go to variable window and choose FolderPath row at end there is expression button so just click on that and in the expression textarea define this expression "@[User::IncrementValue] == 1 ? @[User::FolderPathOne] : @[User::FolderPathTwo]" means when for loop starts looping if Initial Value (IncrementValue) == 1 then consider "FolderPathOne" or else consider "FolderPathTwo".
Set up FileName variable so go to variable window and choose FileName row at end there is expression button so just click on that and in the expression textarea define this expression "@[User::IncrementValue] == 1 ? @[User::FileNameOne] : @[User::FileNameTwo]" means when for loop starts looping if Initial Value (IncrementValue) == 1 then consider "FileNameOne" or else consider "FileNameTwo".
Step 6
In this step we need to configure "FOREACH LOOP" container so select on "FOREACH LOOP" right click and edit -> select collection (Left side) and choose Expression and click.
A window will popup in the property column specify "Directory" and Expression as FolderPath and same way specify another property "FileSpec" and Expression as FileName. as shown in below image.
Step 7
Save everything because we have confiured both containers properly. Now its time to run the project and check the output.
As you see we have successfully executed the project executed two folders using "FOR LOOP" and then files inside each folder executed via "FOREACH LOOP" and passing it to destination data source via ETL process.
Thank you so much friends for reading this article hope you have enjoyed reading and understood implementing FOR LOOP container in SSIS. Any doubts feel free to ask us anytime on the below mentioned contact details.
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.
