SSIS-For Loop container

We will introduce the first container in SSIs 2008: For Loop container,
This container is not used in many practical applications, but it is useful in some special scenarios. It is used to realize the loop of a variable and assign values to the variable in the loop. When the condition for terminating the loop is met
It exits the loop. Its function is similar to the do while structure of the structured programming language. For example, the following structure achieves the effect of adding 1 to 10.
 
A = 1
B = 0
Do while a = 10
B = B +
A = a + 1
End

Purpose:

I have a table named clients, which includes two fields, clientid and clientname. I want to use this for Loop
The container adds 10 records in this table. The client ID column is an identitykey and can be assigned automatically.
I use the for loop container to assign a value to it. The following describes how to create this for loop container.

Now I will use an example to illustrate its usage:

• Create a new SSIS pakcage and name it SSIS containers
• Add two system variables: loopvalue and int32, which are used to save the value of the loop variable we will use in the loop; endvalue and INT 32, which are used to save the end value of the loop, I set it to 10.
• Drag the for loop container from the toolbox to the control flow page.
• Double-click the for loop container to open the for loop editor. The editor page is simple and the most important setting is in the current for loop page. It has three attributes to be set.
  • Initexpression: an initial value expression that is used to assign an initial value to a loop variable. This loop variable can be a package-level variable or a container-level variable, in this expression, I enter @ loopvalue = 1
  • Evalexpression:
    The judgment expression. It accepts a condition judgment Expression and terminates the current loop when the condition expression is not met. When the condition expression is met, execute the following
    The operation of assignexpression until the expression is not satisfied. In this expression, enter @ loopvalue <= @ endvalue
  • Assignexpression: A value expression used to assign values to cyclic variables. In this expression, enter @ loopvalue = @ loopvalue + 1. It is worth noting that the setting of this expression must affect the value of evalexpression, otherwise it will form an endless loop.
• Add an execute SQL task to the for loop container and double-click the Execute SQL task to edit its attributes.
  • Use ole db to connect
  • Select (create) the connection to the database where the clients table is located
  • In SQL statement, enter:

  Insert into DBO. Clients (clientname) values ('name' + convert (varchar ,?))

  • Create a new variable ing in parameter mapping, and Change User: loopvalue, parametername to 0, and data type to long under Varible name.
• Save the SSIS package and run it.
• View the contents of the clients table in SQL Server Management studio. 10 records are added.

Conclusion

In our example, the most important part is to set three loop expressions. In some complicated processing, the values of these expressions may change dynamically, for example, evelexpression, that is, the variable for loop termination, can be not a fixed value.