Skip to main content

OLEDB Command Transformation in Biml

Why Use the OLEDB Command Transformation​

The OLEDB Command transformation runs a SQL statement once per input row and exposes the row values as parameters to that statement. A common use is updating end dates on expired records during a Type 2 dimension load. The trade off is performance: a per row statement is slow compared to staging the input into a temporary table and running a single set based update against the target. Both patterns are valid, and the OLEDB Command works well when the row count is small or when the calling logic must run per row.

What the Transformation Needs​

Three settings drive the transformation:

  • A name and a connection. These are attributes of the 'OledbCommand' node because they apply to the whole transformation.
  • The statement to execute. The statement text goes inside a 'DirectInput' element. Parameter placeholders use the question mark character.
  • A 'Parameters' block that maps input columns to the placeholders in order. Each parameter is defined by a source column, a target column of the form 'Param_N' where N is the zero based position of the placeholder in the statement, and a data type. If the length is omitted on a string type, the package still builds and runs but the designer reports a column synchronization warning.

Inserting Customer Rows​

The example below feeds a customer source query into an OLEDB Command that inserts each row into a staging table. The same shape adapts to UPDATE or DELETE statements simply by changing the statement text and the parameter mapping.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="SalesSource" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2019;Integrated Security=SSPI;Initial Catalog=SalesAnalytics" />
    <OleDbConnection Name="StagingDb" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2019;Integrated Security=SSPI;Initial Catalog=Staging" />
  </Connections>

  <Packages>
    <Package Name="LoadStagingCustomers" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="StreamCustomers">
          <Transformations>

            <OleDbSource Name="ReadCustomers" ConnectionName="SalesSource">
              <DirectInput>
                select
                  CustomerCode as customer_code,
                  GivenName as first_name,
                  FamilyName as last_name
                from sales.Customers;
              </DirectInput>
            </OleDbSource>

            <OleDbCommand Name="InsertEachCustomer" ConnectionName="StagingDb">
              <DirectInput>
                insert into stg.Customers
                  (customer_code, first_name, last_name)
                values
                  (?, ?, ?);
              </DirectInput>
              <Parameters>
                <Parameter SourceColumn="customer_code" TargetColumn="Param_0" DataType="String" Length="15" />
                <Parameter SourceColumn="first_name" TargetColumn="Param_1" DataType="String" Length="50" />
                <Parameter SourceColumn="last_name" TargetColumn="Param_2" DataType="String" Length="50" />
              </Parameters>
            </OleDbCommand>

          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Notes on the Mapping​

The position number in 'Param_0', 'Param_1', and 'Param_2' must match the order of the question marks in the statement. The data type only needs the type name to build, but specifying a length silences the column synchronization warning and keeps the metadata clean for downstream comparisons.