IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

Logging in SSIS with BIML 2 – Logging Package Execution in the Control Flow

This is the second post in a series on on logging in SSIS. In the first post I described logging package and tasks within a package by using an event handler. In this post I’ll describe a second method: Logging Package Execution in the control flow. It’s a very common pattern:

  • At the start of the package create a record in an audit table.
  • Use the identity of this row in the dataflow and add it to every row you insert in the target table.
  • Use several counter to count the rows passing through the dataflow.
  • At the end of the package update the audit row that we created in the first step with the end time and the row counters.

Data Lineage

The most important advantage of this method lies in step 2 by adding a reference to the audit row in every row that we insert into the target table we will be able to track back every row to when and how it was inserted. This has saved my ass several times by be being able to roll back a faulty import 2 weeks ago and repopulate it with the correct data.

Andy Leonard wrote a nice blog post on how you can set this up in SSIS. In this post I’ll focus on creating a similar package with BIML. My package will look like this:

image

The Audit Table

To store the package execution information we need an audit table. Create it with the following statement:

CREATE TABLE meta.SsisAudit(
    AuditID int IDENTITY(1,1) NOT NULL
  , PackageName  varchar(50)
  , PackageGUID uniqueidentifier
  , PackageVersionGUID uniqueidentifier
  , ExecutionID uniqueidentifier
  , StartDate datetime
  , Enddate datetime
  , PackageDuration AS DATEDIFF(second, StartDate
        , EndDate) PERSISTED
  , ExtractRowCount int
  , InsertRowCount int
  , UpdateRowCount int
  , IsProcessed bit DEFAULT(0)
  , CONSTRAINT PK_SsisAudit PRIMARY KEY CLUSTERED
    ( AuditID ASC));
GO

The Stored Procedures

To log the package execution information we’ll use two stored procedures that write the information to the SsisAudit table. The first one is used at the start of the package and will return the id of the inserted row. The second is used to update this row with the end date and the row counters .

CREATE PROCEDURE [meta].[uspNewAuditRow]
    @PackageName varchar(50)
  , @PackageGUID uniqueidentifier
  , @PackageVersionGUID uniqueidentifier
  , @ExecutionID uniqueidentifier
  , @StartDate datetime
AS
INSERT INTO meta.SsisAudit
  ( PackageName
  , PackageGUID
  , PackageVersionGUID
  , ExecutionID
  , StartDate
  , IsProcessed)
VALUES
  ( @PackageName
  , @PackageGUID
  , @PackageVersionGUID
  , @ExecutionID
  , @StartDate
  ,0);

/* Return the id of the inserted row*/
SELECT CAST(SCOPE_IDENTITY() AS int) AS AuditID
GO


CREATE PROCEDURE [meta].[uspUpdateAuditRow] 
    @ExtractRowCount int
  , @InsertRowCount int
  , @UpdateRowCount int
  , @AuditID int
AS
UPDATE meta.SsisAudit
SET EndDate = SYSDATETIME()   
  , ExtractRowCount = @ExtractRowCount
  , InsertRowCount = @InsertRowCount
  , UpdateRowCount = @UpdateRowCount
  , IsProcessed = 1
WHERE AuditID = @AuditID
GO

BIML Snippets

To create the logging facility in an SSIS Package use the following code snippets in the BIMLScript that creates the package:

<!--Variables-->
<Variables>
   <Variable Name="RcExtract"
      DataType="Int32"
       Namespace="User" >0</Variable>
   <Variable Name="RcInsert"
      DataType="Int32"
      Namespace="User" >0</Variable>
   <Variable Name="RcUpdate"
      DataType="Int32"
      Namespace="User" >0</Variable>
   <Variable Name="AuditID"
      DataType="Int32" Namespace="User" >0</Variable>
</Variables>

<Tasks>
   <!--Create audit row and get id-->
   <ExecuteSQL Name="SQL Create AuditRow and Get AuditID"
      ConnectionName="metaDB"
      ResultSet="SingleRow">
      <DirectInput>
EXEC [meta].[uspNewAuditRow] ?,?,?,?,?
      </DirectInput>
      <Parameters>
         <Parameter Name="0" Direction="Input"
            DataType="Int32"
            VariableName="System.PackageName" />
         <Parameter Name="1" Direction="Input"
            DataType="Guid"
            VariableName="System.PackageID" />
         <Parameter Name="2" Direction="Input"
            DataType="Guid"
            VariableName="System.VersionGUID" />
         <Parameter Name="3" Direction="Input"
            DataType="Guid"
            VariableName="System.ExecutionInstanceGUID" />
         <Parameter Name="4" Direction="Input"
            DataType="DateTime"
            VariableName="System.StartTime" />
      </Parameters>
      <Results>
         <Result Name="0"
            VariableName="User.AuditID" />
      </Results>
   </ExecuteSQL>

   <!--In Dataflow:-->
   <Dataflow>
   <Transformations>

    <!--Add meta data –>
    <DerivedColumns Name="DC add meta data">
       <Columns>
          <Column Name="AuditID"
             DataType="Int32"
             ReplaceExisting="false">
@AuditID
            </Column>
         </Columns>
         </DerivedColumns>
      </Transformation>
   </Dataflow>

   <!--Update audit row-->
   <ExecuteSQL Name="SQL Update Audit Row"
      ConnectionName="metaDB"
      ResultSet="None">
      <DirectInput>
EXEC [meta].[uspUpdateAuditRow] ?,?,?,?
      </DirectInput>
      <Parameters>
         <Parameter Name="0" Direction="Input"
            DataType="Int32"
            VariableName="User.AuditID" />
         <Parameter Name="1" Direction="Input"
            DataType="Int32"
            VariableName="User.RcExtract" />
         <Parameter Name="2" Direction="Input"
            DataType="Int32"
            VariableName="User.RcInsert" />
         <Parameter Name="3" Direction="Input"
            DataType="Int32"
            VariableName="User.RcUpdate" />
      </Parameters>
   </ExecuteSQL>

 

ABOUT BIML

You can leverage Business Intelligence Markup Language – or BIML – to automate the creation of Microsoft SQL Server Integration Services (SSIS) Packages. BIML is a creation of Varigence, Inc. and is available in proprietary products, open source projects, and has been published as an open language specification. The popular open source BIDSHelper project includes Biml functionality, enabling anyone to write and execute Biml code for free.

Do you need a head start with the automation of SSIS packages: consider my BIML Workshop.

» Similar Posts

  1. Logging in SSIS with BIML 1 – Logging Task Execution
  2. Creating a Meta Data Driven SSIS Solution with Biml - 3. Creating a Table Package
  3. Creating a Meta Data Driven SSIS Solution with Biml - 4. Creating the Table Packages

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed