BIML, Stored Procedures and Return Parameters

Despite the improved logging features that have been incorporated into SQL2012+, I am a great fan of defining your own logging methods.  The most common method for this is having a package recognised by an ‘audit key’ and then using that key as a base point to record information that you want throughout the package.  For example, get the key (at the same time record the package start), then record success, failure, row counts, variable values etc. 

When you use a master package to execute children, you associate the childs execution (key) with a master execution (key) and this opens up some really great logging.  We’ll look at how to pass those keys down to the child in the next post, but for now, lets look at how we generate and store a package key in BIML.  In our environment, we’ve got a ‘master table (MASTER_EXEC) to record (master) package execution and a stored proc (MASTER_EXEC_ID_GET) to return the identity value (when ever we fire the proc).  As a minimum, we pass the package name to that proc so we can identify the package that was run.  You can see the format of the table and the creation scripts below. 

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

If we wanted to test this proc in SSMS, we’d use some syntax like;


DECLARE @RET INT;
EXEC @RET = [dbo].[MASTER_EXEC_ID_GET] 'Here is Some TEST Name'
PRINT @RET

Now, for the BIML.  Here, we just have to define a variable in the packages scope to hold the key  (Ive used a variable called MASTER_ID), and execute the proc.  Procedure execution occurs through an ExecuteSQL task and we assign (and capture) the stored procs parameter values through the ExecuteSql tasks parameters node.


<Biml xmlns="<a href="http://schemas.varigence.com/biml.xsd"">http://schemas.varigence.com/biml.xsd"</a>>
 <Connections>
  <OleDbConnection Name="BIML_DB" ConnectionString="Provider=SQLNCLI11;Server=(myserver);Integrated Security=SSPI;Database=BIML" />
 </Connections>
 <Packages>
  <Package Name="Master_3" ConstraintMode="Linear">
   <Variables>
    <Variable Name="MASTER_ID" DataType="Int32">-1</Variable>
   </Variables>
  <Tasks>
   <ExecuteSQL Name="AUDIT_KEY" ConnectionName="BIML_DB">
    <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>

You can see that the SQL statement (in BIML) mirrors what we would otherwise do in SQL.  The BIML parameter definition does not need to specify the parameters by name (rather by their ordinal position in the SQL statement) and we also specify the direction.  This mimics exactly what we would do in SSIS.