OLEDB Command with Return Variables in Biml

Why Return Parameters Matter

The OLEDB Command transformation can call a stored procedure for each row of the data flow, and the procedure can return values through output parameters. Those return values can be written back onto the row as it travels through the buffer, which means downstream transformations can consume them like any other column. A common case is delegating row level logic to a procedure that performs an insert, computes an audit value, and returns the audit value for use later in the pipeline.

The Stored Procedure

The procedure below inserts a customer row and returns the first character of the family name as an output parameter:

create procedure dbo.UpsertCustomer
(
  @customer_code nvarchar(15),
  @first_name nvarchar(50),
  @last_name nvarchar(50),
  @last_initial nvarchar(1) output
)
as
begin
  set nocount on;

  insert into dbo.Customers (customer_code, first_name, last_name)
  values (@customer_code, @first_name, @last_name);

  set @last_initial = LEFT(@last_name, 1);
end

Wiring the Procedure into the Transformation

Two differences set this configuration apart from the basic OLEDB Command. First, the parameter mapping uses 'TargetColumn' values that match the procedure parameter names instead of generic 'Param_N' tokens. Second, the output parameter carries a 'Direction' attribute set to 'Output'. The default direction is input.

<OleDbCommand Name="CallUpsertCustomer" ConnectionName="StagingDb">
  <DirectInput>
    exec dbo.UpsertCustomer
      @customer_code = ?,
      @first_name = ?,
      @last_name = ?,
      @last_initial = ? output;
  </DirectInput>
  <Parameters>
    <Parameter SourceColumn="customer_code" TargetColumn="@customer_code" DataType="String" Length="15" />
    <Parameter SourceColumn="first_name" TargetColumn="@first_name" DataType="String" Length="50" />
    <Parameter SourceColumn="last_name" TargetColumn="@last_name" DataType="String" Length="50" />
    <Parameter SourceColumn="last_initial" TargetColumn="@last_initial" DataType="String" Length="1" Direction="Output" />
  </Parameters>
</OleDbCommand>

The row entering the OLEDB Command must already contain a column for the return value. A Derived Column transformation upstream of the command supplies that placeholder.

Full Data Flow

The full package sources customer rows, adds a placeholder column for the return value, calls the procedure for each row, then writes the rows including the returned initial to the destination:

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

  <Packages>
    <Package Name="LoadCustomersWithReturn" 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>

            <DerivedColumns Name="AddReturnPlaceholder">
              <Columns>
                <Column Name="last_initial" DataType="String" Length="1">NULL(DT_WSTR, 1)</Column>
              </Columns>
            </DerivedColumns>

            <OleDbCommand Name="CallUpsertCustomer" ConnectionName="StagingDb">
              <DirectInput>
                exec dbo.UpsertCustomer
                  @customer_code = ?,
                  @first_name = ?,
                  @last_name = ?,
                  @last_initial = ? output;
              </DirectInput>
              <Parameters>
                <Parameter SourceColumn="customer_code" TargetColumn="@customer_code" DataType="String" Length="15" />
                <Parameter SourceColumn="first_name" TargetColumn="@first_name" DataType="String" Length="50" />
                <Parameter SourceColumn="last_name" TargetColumn="@last_name" DataType="String" Length="50" />
                <Parameter SourceColumn="last_initial" TargetColumn="@last_initial" DataType="String" Length="1" Direction="Output" />
              </Parameters>
            </OleDbCommand>

            <OleDbDestination Name="WriteCustomers" ConnectionName="StagingDb">
              <ExternalTableOutput Table="dbo.CustomersAudit" />
            </OleDbDestination>

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

The placeholder column from 'AddReturnPlaceholder' is what the OLEDB Command writes the procedure output into. After the command runs, the row carries the original three input columns plus the populated 'last_initial' column, which the destination then writes to the audit table.