Executing Stored Procedures and Return Parameters in Biml

Why This Pattern Matters

Custom audit logging usually starts with a single integer key. The package calls a stored procedure, the procedure inserts an audit row, and the new identity value comes back as a return value. Every later log entry, error, or row count then references that key. The key has to be captured into an SSIS variable so the rest of the package can use it.

This walkthrough shows the Biml that wires an Execute SQL Task to a stored procedure and binds the procedure's return value to a package variable.

The Audit Procedure

A small audit table records the package name and start time. The 'GET' procedure inserts the row and returns the identity it just created.

CREATE TABLE dbo.MASTER_EXEC (
  RUN_ID    int IDENTITY(1,1),
  JOB_NAME  nvarchar(256),
  START_DT  datetime DEFAULT (GETDATE())
);

CREATE PROC dbo.MASTER_EXEC_ID_GET
  (@JOB_NAME nvarchar(256))
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.MASTER_EXEC (JOB_NAME) VALUES (@JOB_NAME);
  RETURN IDENT_CURRENT('dbo.MASTER_EXEC');
END;

A quick smoke test in SSMS confirms the procedure returns the new identity:

DECLARE @RET int;
EXEC @RET = dbo.MASTER_EXEC_ID_GET 'Nightly Run';
PRINT @RET;

The Biml

The package declares an integer variable to hold the captured key, then runs the procedure through an Execute SQL Task. The 'DirectInput' uses positional parameter markers, and 'Parameters' inside the task assign each marker by ordinal position. 'Direction="ReturnValue"' tells SSIS to bind the procedure return value to the variable, and the package name is passed in as input.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="AuditDb"
      ConnectionString="Provider=SQLNCLI11;Server=(local);Integrated Security=SSPI;Database=Audit" />
  </Connections>
  <Packages>
    <Package Name="MasterAuditExample" ConstraintMode="Linear">
      <Variables>
        <Variable Name="MASTER_ID" DataType="Int32">-1</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="AUDIT_KEY" ConnectionName="AuditDb">
          <DirectInput>EXEC ? = dbo.MASTER_EXEC_ID_GET ?</DirectInput>
          <Parameters>
            <Parameter VariableName="User.MASTER_ID"     Name="0" DataType="Int32"      Direction="ReturnValue" />
            <Parameter VariableName="System.PackageName" Name="1" DataType="AnsiString" Length="256" Direction="Input" />
          </Parameters>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The 'DirectInput' SQL mirrors the SSMS test exactly. Biml does not name the parameters; they map by their ordinal position in the SQL string. Once the task runs, 'User.MASTER_ID' holds the new audit key and the rest of the package can pass it to logging tasks, write it to row count tables, or hand it down to a child package.