• Marek Vavrovic

For Loop Container in SSIS

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





10 views0 comments

Recent Posts

See All