The Foreach ADO Iterator in Biml

Why Use a Foreach ADO Loop

The OLE DB Command transform iterates row by row inside a Dataflow and is fine for a single SQL statement. For more complex per-row logic, the Foreach ADO loop in the control flow gives a container that can hold multiple tasks, supports precedence constraints between them, and runs the entire container body for each row in a recordset variable. Field values from each row map to package variables that other tasks can read.

The Pattern

The package has three moving parts:

• An ExecuteSQL task that returns a recordset and stores it in an Object variable.
• A ForEachAdoLoop container that consumes the recordset variable and assigns each row's columns to its own variables.
• One or more tasks inside the container that use the per-row variables.

The recordset query is small. It returns subcategory IDs and names from the source database.

select ItemSubcategoryID as sub_cat_id
     , Name as sub_cat_name
from Production.ItemSubcategory;

The first ExecuteSQL task assigns the result set to sub_cat_results. As the loop iterates rows, the loop assigns sub_cat_id and sub_cat_name. Those two variables are scoped to the loop container so the package scope stays clean. Tasks inside the container then accept the variables as parameters.

select *
from [Production].[ItemSubcategory]
where ItemSubcategoryID = ?;

Writing the Biml

A few details about the Biml are worth calling out. The recordset variable is declared as a child of the Package because it must be visible to both the read task and the loop. The two loop variables are declared as children of the ForEachAdoLoop. The ExecuteSQL that returns the recordset sets ResultSet to Full and uses Result Name 0 to bind to the recordset variable. The ForEachAdoLoop sets SourceVariableName to the recordset variable and uses VariableMappings to bind the row column ordinals to the loop variables.

Variable references inside the loop's tasks use the fully qualified path, which makes the package's intent explicit even if the user-only path would also work.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="retail_source" ConnectionString="Provider=SQLOLEDB;Data Source=.\sql2019;Integrated Security=SSPI;Initial Catalog=RetailSource" />
  </Connections>

  <Packages>
    <Package Name="iteration_01" ConstraintMode="Linear">

      <Variables>
        <Variable Name="sub_cat_results" DataType="Object" />
      </Variables>

      <Tasks>
        <ExecuteSQL Name="read_dim" ConnectionName="retail_source" ResultSet="Full">
          <DirectInput>select ItemSubcategoryID as sub_cat_id, Name as sub_cat_name from Production.ItemSubcategory;</DirectInput>
          <Results>
            <Result Name="0" VariableName="User.sub_cat_results" />
          </Results>
        </ExecuteSQL>

        <ForEachAdoLoop Name="for_each_record" SourceVariableName="User.sub_cat_results" ConstraintMode="Parallel">
          <Variables>
            <Variable Name="sub_cat_id" DataType="Int32">0</Variable>
            <Variable Name="sub_cat_name" DataType="String" />
          </Variables>
          <VariableMappings>
            <VariableMapping Name="0" VariableName="iteration_01.for_each_record.User.sub_cat_id" />
            <VariableMapping Name="1" VariableName="iteration_01.for_each_record.User.sub_cat_name" />
          </VariableMappings>

          <Tasks>
            <ExecuteSQL Name="select_id" ConnectionName="retail_source">
              <DirectInput>select * from [Production].[ItemSubcategory] where ItemSubcategoryID = ?;</DirectInput>
              <Parameters>
                <Parameter Name="sub_cat_id" DataType="Int32" Direction="Input" VariableName="iteration_01.for_each_record.User.sub_cat_id" />
              </Parameters>
            </ExecuteSQL>

            <ExecuteSQL Name="select_name" ConnectionName="retail_source">
              <DirectInput>select * from [Production].[ItemSubcategory] where [Name] = ?;</DirectInput>
              <Parameters>
                <Parameter Name="sub_cat_name" DataType="String" Length="50" Direction="Input" VariableName="iteration_01.for_each_record.User.sub_cat_name" />
              </Parameters>
            </ExecuteSQL>
          </Tasks>
        </ForEachAdoLoop>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The container's child Tasks block holds whatever logic should run per row. Any task type works inside the loop, not just ExecuteSQL. Precedence constraints inside the container behave the same way they do at the package level.