EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SSIS Tutorial SSIS For Loop
Secondary Sidebar
SSIS Tutorial
  • SSIS Basic and Advanced
    • SSIS Merge Join
    • SSIS Sequence Container
    • SSIS Foreach Loop
    • SSIS Toolbox
    • SSIS Package Configuration
    • SSIS Variables
    • SSIS Expression
    • SSIS For Loop
    • SSIS Replace
    • SSIS SFTP
    • SSIS Data Types

SSIS For Loop

SSIS For Loop

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

1

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 4

step 4-1

step 4-2

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.

step 5

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 5-1

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.

step 6

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 –

  1. For Loop in PLSQL
  2. NumPy for loop
  3. PL/SQL FOR Loop Cursor
  4. Rust for loop
Popular Course in this category
Data Scientist Training (85 Courses, 67+ Projects)
  85 Online Courses |  67 Hands-on Projects |  660+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more