Foreach Loop container in SSIS allows you to loop over the values of the
object variable and store the necessary information as a result set to
variables to be used anywhere in SSIS for different purposes.
1- Create a object variable and fill it with information:
See: SSIS – Storing Dataflow results to Object Variable with Recordset Destination
See: SSIS – Mapping Full Resultset with Execute SQL Task to Object Variable
(Both examples above fill Object variable with sys.tables data within the msdb database in SQL server.)
2- Create two more variables to hold the table name (string) and object id (integer)
3- Drag&Drop a Foreach Loop container to Dataflow:
4- Go Collection tab and Select “Foreach ADO Enumerator” for Enumeration
and select your ObjVariable as “ADO Object Source Variable” and specify
“Enumeration Mode” as “Rows in the First Table”
5- Go to Variable Mappings Tab and add your map your variables to relevant index of your data in the Object Variable
Variable index is zero based and must be index of the column in the
dataset. We use Tablename and objectId which indexes are 0,1 in order.
6- Drag&Drop Script Component to see the values mapped and run the package.
