This is already becoming a FAQ: how can you use a For Each Loop in an SSIS package to loop through a set of rows.

Here's one way to do it:

1. Use an Execute SQL Task to:

- call a statement. I used the following in the AdventureWorksDW database:

          SELECT PromotionKey FROM DimPromotion

- put the result set in a variable (Lets call it RecordsetVariable)

2. Next, use a For Each Loop that uses a 'For Each ADO Enumerator' to loop over the recordset in RecordSetVariable.

3. In the loop, have a script task that basically takes the fields you're interested in and puts it into a variable:

Public Sub Main()

      Dim recordset As ADODB.Recordset

      ' todo: error checking for types & nulls.

      recordset = CType(Dts.Variables("RecordsetVariable").Value, ADODB.Recordset)

      ' take the value out of the first field and put it into a variable for PromotionID

      Dts.Variables("PromotionID").Value = CInt(recordset.Fields(0).Value)

      Dts.TaskResult = Dts.Results.Success

End Sub

Note that in order to access RecordsetVariable and PromotionID, they need to be added to the 'ReadOnlyVariables' and 'ReadWriteVariables' properties on the script.

4. The variable now contains the field value of interest. This can be used in a subsequent parameterised OLEDB Source adapter statement.

that's it!