Updated March 15, 2023
Definition of SSIS For Loop
The For Loop Container will repeat the SQL Integration Services activities in SSIS until the supplied expression is False. In every programming language, it’s the same as the For Loop. The For Loop Container is a looping container that repeats itself until a condition is true.
What is SSIS for loop?
Containers are SSIS objects that aid in organizing one or more tasks. For example, these can assist us in looping over a series of tasks till a requirement is reached or conceptually grouping a set of tasks. Containers can also be nested, with one container within another. For example, the looping implementation of a task is a For loop task, which evaluates an expression and loops through the procedure till the result turns False.
1. Rename the standard packages to ForLoopContainer.dtsx in a new SSIS project called Container.
2. Establish a new variable called Counter in the ForLoopContainer.dtsx package. If the Parameters window is still not open, they may need to open it. Right-click the design pane and select Variable, or select the Variables icon in the top right corner of the package developer screen.
3. Click the Add Variable button once the window has opened. Accept the variable’s defaults (int32) and a default numeric value of 0.
3. To launch the editor, drag the For Loop Container to the Control Flow and double-click it.
SSIS for loop Configure
We wish to run the same SQL statement(s) in several database servers/instances using SSIS.
Here’s a strategy:
The solution takes three steps:
1. Load all target DBs’ connection strings into an SSIS object type variable within a package.
2. The content of the object variable and iterate through each connection string using a ForEachLoop container.
3. The rest is simple: inside the ForEachLoop container, place the Execute SQL task(s) with the SQL statements that must run in all target instances.
Use the power of SSIS expressions as the only answer! Add an expression to the inner Execute SQL Task(s) Connection manager to change the connection string with the content of a variable generated by the For Loop container on each iteration. Throughout this example, let the connection strings in a SQL database, but then we could store them in a flat file or anywhere else that SSIS can connect to. Sometimes, writing a query to give out all the instances may be usual, but I like to save the values to have specific control over the task.
SSIS for loop Container
Let’s say we have two source folders, one for “silver id” and the other for “RUPAY ID,” with many data source files coupled with “silver id” and “RUPAY ID” source files inside each directory. So, we like to loop two directories but still only consider “silver id” and “RUPAY ID” naming multiple source files for the ETL process because we would not want to take the rest of the quasi-file types.
Step-1
Suppose we use a simple File Spec (silver id *) to repeat source files within both directories, we can only loop source files inside that one folder. It cannot return to a root directory to cycle over a separate folder with a different File Spec (Rupay ID *). Therefore, only “FOR LOOP” was used to pass the directory path and File Spec, i.e. (silver id *) and (RUPAY ID *).
Step-2
Here Are two folders to loop over the files, Which are shown below.
Step-3
Drag and drop the FOR-LOOP container to the control tab, then drag the entire for each loop inside the for-loop container. We’ll examine how to use the For Loop Container task choices in SSIS once the project has been built. Drag and drop the For Loop Container shown on the screen below once we’ve built a project.
Step-4
Then, add an Execute SQL job to insert some records into a table. We’ll utilize the Loop container and insert it into the table every time it loops. The result of adding the Execute SQL job inside the for-loop container is seen on the screen below. This Execute SQL task will be used to INSERT data into the SQL table.
Step-5
We’ll build up the For Loop Container; first, we’ll add two variables used during the loop process. To make a variable, navigate to View –> Other windows –> Variable and fill in the values below. The for-loop statement has:
InitExpression: The loop starts with an initialization expression; the counters variable should be initialized first (for example, @counter = 1 or @i = 1).
EvalExpression: The counter variable’s value when compared to the precondition. If the condition is True, the For-loop element will be executed. Eventually, the For loop will be ended.
Assign Expression: This statement will be executed at the end of each iteration. It allows us to adjust the counter parameter according to our needs.
Then, please type in the For Loop Editor the values we want. The For Loop will begin at 0 and run until it hits 10, increasing by 1 for each iteration.
Step-6
To run a SQL task in SSIS, we must set the following properties for each parameter:
Select the variable name that wants to translate to a parameter from the drop-down menu.
Direction: Indicate whether the parameter’s type is integer or floating-point (input, output, return value)
When we query the table, we see that our for loop performed as expected. Therefore, the table we created here is:
CREATE TABLE Airline (
Aid INT NOT NULL,
Airlinename VARCHAR(20) NOT NULL,
service INT,
City varchar (20),
PRIMARY KEY (Aid),
UNIQUE (Airlinename)
);
To see if the Package was properly executed, go to the query analyzer and look at the results below.
Conclusion
Coming to an end, we have seen how for loop is worked in SSIS with an implementation. A for loop will perform the tasks a set number of times, such as 12 or 20, and the number of times is provided in the container’s specification. For each item in the collection of items that this is looking at, for each loop will run once, which we have seen in my previous article.
Recommended Articles
This is a guide to SSIS For Loop. Here we discuss the definition, What is SSIS for loop, SSIS for loop Configure, SSIS for loop Container. You may also have a look at the following articles to learn more –
