SSIS For Loop Container

<-- SSIS Execute Process TaskSSIS Foreach Loop Container -->

In the previous sections, we already learnt Sequence Container. In the section, we'll learn the second container - For Loop Container. For Loop Container like the for statement in a programming language is used to execute the tasks inside the container repeatedly until the condition returns false. Like for statement, the container defines the following 3 For Loop properties in the For Loop Editor.

For Loop PropertiesDescriptionOptionalExample
InitExpressionInitialize the loop counter before the loop starts.Yes@[User::Count] = 1
EvalExpressionThe loop keeps running until the expression returns false.No@[User::Count] < 6
AssignExpressionIncrements or decrements the loop counterYes@[User::Count] = @[User::Count] + 1

Note:

  • EvalExpression must be defined in the container and the other 2 are optional.
  • There is no break task to jump out the container like other programming languages.
  • The assignment operator (=) with the syntax @Var = <expression> can be used in InitExpression and AssignExpression.
  • Nested loops are supported and that means sub for loop container can be defined in another container.

In the following example, we'll create a package to create 5 sub folders by using For Loop Container.

  1. Create an empty package ForLoopContainer.dtsx in the LearnSSIS1 project and open it in design mode.

  2. Create 2 variables as follows.

    3. SSIS 2 Variables Definition With an Expression


  3. Drag For Loop Container in SSIS Toolbox and drop to the package and drag the File System Task to the For Loop Container.

    4. For Loop Container With A System File Task


  4. Right click the For Loop Container and choose "Edit..." to open the For Loop Editor window. Then set the For Loop properties as follows.

    5. SSIS For Loop Editor


  5. Click "OK". Then right click the File System Task in the container and choose "Edit..." to open the File System Task Editor.

  6. Set the properties below in General tab and click "OK" button to save the settings.

    7. File System Task Editor Settings


  7. Execute the package after click the "Save" icon to save the package.

    8. You will find 5 folders F1, F2, F3, F4 and F5 were created in the folder C:\SSIS.


<-- SSIS Execute Process TaskSSIS Foreach Loop Container -->