Update: Kirk Haselden has a great example of this that I found right after I posted this. http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
There’s been a number of questions about iterating through a collection of items and performing an operation for each item in the SSIS forums recently, so I put together a quick example of doing this.
The example package retrieves a list of parent rows from one table, then uses a Foreach Loop to iterate through each parent row. It uses the ID from the parent row to retrieve the matching child records from a second table.
This example assumes the following tables in your database:
CREATE TABLE [dbo].[ChildTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[ChildName] [varchar](25) NULL,
CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED
( [ID] ASC )
CREATE TABLE [dbo].[ParentTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentName] [varchar](25) NOT NULL,
CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED
( [ID] ASC )
It also assumes that the ParentID in ChildTable points to the IDs in ParentTable.
The attached package starts with an Execute SQL tasks that retrieves the records from the ParentTable and stores it in a variable named “parentResultset”. The Foreach Loop container is set to use the “parentResultset” variable as the source variable for enumeration. It maps the ID column from the “parentResultset” variable to the “parentID” variable on the Variable Mappings tab.
There is a variable named “select”. This variable uses an expression to build the following select statement: “SELECT ID, ParentID, ChildName FROM ChildTable WHERE ParentID = ” + the “parentID” variable. The “select” variable is then used in another Execute SQL task.
Hopefully this helps illustrate how to use the Foreach Loop.