The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
Options
InitExpression Optionally, provide an expression that initializes values used by the loop.
EvalExpression Provide an expression to evaluate whether the loop should stop or continue.
AssignExpression Optionally, provide an expression that changes a condition each time that the loop repeats.
Name Provide a unique name for the For Loop container. This name is used as the label in the task icon. Object names must be unique within a package.
Example 1: in this example I am going to use an Insert Statement to import new rows into this table.
Step 1: bring the For loop container in and define a variable @i for the container. The variable value is set to zero.
Step 2: Set how many times will the container execute. In thi example 11x, the increment is set to 1.
Step 3: Bring the Execute SQL task inside the For Loop container.
Result set: none
Connection: connect to DB
SQLSourceType: Direct input
SQL Statement: INSERT INTO SSISForLoop
(CounterNumber, LoopNumber, Updated)
VALUES
(?, 'Coca-Cola ' + CAST(? AS varchar(30)), GETDATE())
Notice that we have two question marks. Both using the same variable. Must be mapped twice.
Step 4: I have variable comming from the container and I want to use it to insert the values into SQL table. Go to Parameter Mapping and map the variable: 2 times, because I am using this variable 2 times in the Insert statement. (Id column is set as IDENTITY(1,1)
0=CounterNumber
1=LoopNumber
Step 5: Run the package
Example 2: I want to export these data into .csv files. I have 8 catagories and each category will be exported into separate .csv file.
Step 1: Define variables
Variable for csv files: @FolderPath
Variable to store csv file names: @FilePath
Variable for the Container: @LoopCounter
Variable to hold max CatogoryID: @MaxCategoryID
Step 2: I want to retrieve the max categoryID from Sql table using Execute SQL Task. This variable will be used in For loop Container for setting up the max number of looping.
ResultSet: Single row
SQLSourceType: Direct input
SQLStatement: Select MAX(CategoryID) from Products
Step 3: Execute SQL Task will return a result set. I need to go to Result set tab and map that result set comming from the SQL server to a SSIS variable @MaxCategoryID. I have just one variable from server, Result Name will be 0.
Step 4: Configure For Loop Container. Initial value is set to 1, max numbero of looping comes from SQL server variable @MaxCategoryID. I have 8 categories.
Step 5: Add Data flow task. This task will export the data from Sql server into csv files.
Step 6: Add OLE DB Source, connect to DB.
Use Sql commnad
select [ProductID],
[ProductName],
[CategoryID],
[QuantityPerUnit],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder]
from Products
where CategoryID=?
click on Parameters. Use @LoopCounter variable.
Step 7:
7.1 Add Flat File Destination
7.2 click New
7.3 Choose formate : Delimited
Step 8: in the destination folder I:\Files create one .csv file called Category1.csv to initiate the loop.
Step 9: Click on Browse to connect to the file.
text qualifier: " [all the columns will be separated like this "col1""col2"....
check: Column names in the first data row.
Click on preview, mappings...
Step 10:
10.1 click on the Flat file connection manager, press F4 to go to the Properties
10.2 click on the Expressions [....]
10.3 in the Property Expressions Editor choose ConnectionString
10.4 click on the dots to launch Expression Builder
File name is hardcoded, I want to build the csv file name dynamically .
Step 11: ""+ @[User::FolderPath] +"\\Customer "+ (DT_WSTR, 12) @[User::LoopCounter] +".csv"
expression for a dynamic file name.
Step 12: Run the package
