This write-up discusses one of the core important topic in SSIS. This write-up is divided in two partswhere learning will be around (For & Foreach) Loop, Variables & Expression and practical implementation is inspired from
Mumbai classroom MSBI training and it is written by training candidate.
Why we need For Loop in our SSIS package?
- The reason we need “For Loop” in our SSIS package is that when we have multiple files and all those files have to be gone through the process of ETL, we cannot write the ETL process for every single file instead we create a “For Loop” which will loop through all the files and complete the ETL process with less effort.
What is the difference between For Loop & Foreach Loop?
- “For Loop” will loop through a fixed count. Once the count is stopped the loop will also stop.
- Whereas “Foreach Loop” it will loop through a collection. Now that collection can be anything files in a folder, sub-folders inside folders, recordset etc.
We do put the Data Flow task which contain ETL logic for single file into our “Foreach Loop” so that it can loop through.
What are variables & why do we use it?
- Variables are nothing but they are temporary storages where we can store some value.
- During production we will have to load multiple files into our SSIS package and these files are present in a folder. So to provide the full file path of our file present in a folder we will use Variables.
- The Control Flow has to pass the full file name with its path to the Data Flow and that can be done by using Variables.
How create and set variables?
In order to create variable we have to do right click anywhere on the white part in the Control Flow.
It will open “Variables” window over here set “FullFilePath” as variable give suitable name to it and then select Data type as “String”.
After that we configure the variable in the Foreach Loop. Now click on Edit
Once the Editor window is open then do the following: -
Click on “Collection” tab on the left and on the right side set Enumerator >Foreach File Enumerator.
Select the folder path by browsing it. Followed by type of files to be selected which are to be selected and are recognized by their extension like .csv or .txt. So here selected type is “*.txt”.
Next click on “Variables Mappings” tab on the left and then on the right side select the created Variable from the dropdown as shown in the image down below.
Then click on OK.
This is the end of part 1 write-up. Hoping that For loop, Foreach loop and variables topics is helpful in understanding.