Updated March 15, 2023
Definition of SSIS Foreach Loop
The following article provides an outline for SSIS Foreach Loop. A Foreach loop is identical to a for loop except that it does not have an evaluation phase and one of the control flow components. That’s because we’re looping over just a collection of items and executing a specified expression Foreach object in the collection. So, for example, suppose we have a list of people and need to loop over them without needing to evaluate them or utilize any counters (not an index-based loop).
What is the SSIS Foreach loop?
In an SSIS package, the Foreach Loop container defines a repeated control flow. With Simplest Words, FOREACH LOOP is a sequence tab in the SSIS toolbox and generally performs any assigned tasks. For example, if we have a huge number of source files and would like to load them in it at the same time, we could use the FOREACH loop. Because the FOREACH loop goes across things in a folder or record set, using the FOREACH LOOP CONTAINER is recommended when there is a collection of source files in a folder. Multiple files, directories, and records are loaded and traversed using the for-each loop container.
SSIS Foreach Loop Container
A package can use the Foreach Loop container to repeat the control flow Foreach member of an enumerator. We must attach each flat file in the folder to the Foreach Loop container we add. As SSIS uses more use case scenarios and requires complex configuration Foreach loop gets risky sometimes.
In the SSIS Foreach Loop Container, there are several sorts of enumerators. Inside the SSIS Foreach Loop Container editing form, we can select the enumerator type from the collection tab.
1. Foreach item enumerator: Loop over a list of things that can be manually defined in SSIS. Editor for the Foreach Loop Container
2. Foreach File Enumerator: Loop overall contents in a given directory.
3. Loop over file entries in an ADO Recordset Foreach the ADO enumerator.
4. ADO.NET Schema: Gives a specification for a data source in a table with a row-wise enumerator.
5. Foreach from Variable Enumerator: Loop through the elements stored in an object-type SSIS variable (enumerable)
6. Foreach Node List enumerator: Makes iteration concerning XML output files.
7. Foreach SQL Server Management Objects enumerator: This iterates over objects.
8. Loop over files in a Hadoop distributed file system directory with the Foreach HDFS File Enumerator.
Add the Filesystem task.
The File System Task performs operations on server files and directories. Although we can construct a task to create, copy, delete, or relocate a file or directory, for instance, all of the task’s actions require a source, and some of them, such as copy and transfer, also require a destination.
To open the File System Task Editor, double-click the task.
Set the following values Foreach property in the File System Task Editor:
Set IsDestinationPathVariable to True if you’re using a variable to specify the target folder.
When the IsDestinationPathVariable property is set to True, the Destination Variable property is active. Select
- User::DestinationFolder variable—or whichever variable is created for the destination folder—from the drop-down list.
- Overwrite destination: If this parameter is set to False, the copy operation will fail if there is indeed a file inside the destination folder with the same name as the file transfer. To overwrite the file in the destination folder, set the attribute to True.
- Name: The name we would like to give this job is specified by this property.
- Description Type: The description is specified by this property.
- Operation: This property’s operation is as follows: copy, delete, rename, etc.
To move the processed file to the archive folder, use the File System Task. Fill in the blanks with the properties shown below:
Different types of loops
Data warehousing and preparation systems use loop functions, such as SQL Server Integration Services.
Loops come in a variety of shapes and sizes, including:
– While
– Do…While
– For
– While
In this part, we’ll quickly go through the for loop and the iterator loop to help users grasp SSIS Loop Containers. Within SSIS, there are a variety of approaches for implementing loops in various scopes:
Flow Control
SSIS Container For Loops
SSIS Foreach Loop Container Data Flow Task Within a script component is within a script task.
FOR LOOP:
The Foreach Loop container could be used to group jobs together and repeat the same logic several times. In addition, the For Loop container is a slightly better but very important container for regulating logic flow. In SSIS, the For Loop container is another option to run recurring logic. Furthermore, the For Loop container gives extra ETL flexibility by employing a value-based iterative pattern akin to the for() loop in structured programming languages.
The ETL logic of the Loop container is executed zero to n times. It has three functions that govern the number of times the loop is run:
InitExpression: This expression sets a variable’s initial value (marking the counter variable to zero).
EvalExpression: This expression determines whether or not the loop should continue. This was one of only the three aspects discussed here that needs value; the others are optional.
Assign Expression: This includes great assignment expressions like incrementing the loop counter.
For-each Example
The processes for looping through the files are listed below.
Drag the Foreach Loop Container to the control flow of an SSIS project.
We’re utilizing three variables here: one for the whole path to the file, another for the suffix, and the final for the folder.
Assign the enumerator to Foreach enum and use the variables by clicking a Directory which is shown below:
If we wish to visit the subfolders, check the Traverse Subfolder box.
Set the File Path variable, which will loop through the files in the Folder and Subfolder given by clicking on Dynamic Mapping.
Conclusion
We used the Foreach Loop container in this article to cycle through a folder of files and shifted them one at a time to some other directory. Therefore, we have learned how to use SSIS.
Recommended Articles
This is a guide to SSIS Foreach Loop. Here we discuss the definition, What are the SSIS Foreach loop, the SSIS Foreach Loop Container, and Different types of loops. You may also look at the following articles to learn more –
