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

Logging in SSIS with BIML 1 – Logging Task Execution

The project deployment model in SSIS (2012) offers great default logging functionality. However you may find yourself in need of additional functionality, especially if you use the package deployment model or an earlier version of SQL Server. In this series on logging I dive into 3 methods:

  1. Logging Task Execution using the OnPostExecute Event
    (this post)
  2. Logging Package Execution in the control flow.
  3. Logging Errors using the OnError Event.

In this post I’ll describe the first method: Logging Task Execution using the OnPostExecute Event. This event is fired after the execution of each task and package. And thus provides a great way to log relevant information like package duration and row counts in the data flow.

The SsisEventLog Table

To store these events we need a table:

CREATE TABLE  meta.SsisEventLog (
      EventID int IDENTITY(1,1) NOT NULL
    , ExecutionID uniqueidentifier 
    , EventLogDate datetime 
    , PackageStartDate datetime 
    , PackageDuration AS datediff(second, PackageStartDate
        , EventLogDate) PERSISTED
    , PackageGUID uniqueidentifier 
    , PackageVersionGUID   uniqueidentifier 
    , PackageName  varchar(50)
    , TaskGUID   uniqueidentifier 
    , TaskName   varchar(50)
    , ExtractRowCount int 
    , InsertRowCount int 
    , UpdateRowCount int 
    , CONSTRAINT  PK_SsisEventLog 
        PRIMARY KEY CLUSTERED (EventID)
)

Some remarks:

  • The PackageDuration column is calculated using the datediff function. It calculates the difference in seconds between the time the event was fired and the start date of the package.
  • If the TaskGUID equals to the PackageGUID the event is fired directly after the package was executed.
  • The row count columns will be filled by variables we define in the package and we’ll have to find a way to reset them after logging the event.

To log this information we’ll use a stored procedures that writes the information in the SsisEventLog table and returns a row with 3 columns, all with the value of 0 to reset the variables after logging.

CREATE PROCEDURE [meta].[uspLogEvent]      
      @ExecutionID [uniqueidentifier]
    , @PackageStartDate [datetime]
    , @PackageGUID [uniqueidentifier]
    , @PackageVersionGUID [uniqueidentifier]
    , @PackageName [varchar](50)
    , @TaskGUID [uniqueidentifier]
    , @TaskName [varchar](50)
    , @ExtractRowCount [int]
    , @InsertRowCount [int]
    , @UpdateRowCount [int]
AS       

INSERT INTO [meta].[SsisEventLog](
      ExecutionID
    , EventLogDate
    , PackageStartDate
    , PackageGUID
    , PackageVersionGUID
    , PackageName
    , TaskGUID
    , TaskName
    , ExtractRowCount
    , InsertRowCount
    , UpdateRowCount
)
VALUES(
      @ExecutionID
    , SYSDATETIME()   
    , @PackageStartDate
    , @PackageGUID
    , @PackageVersionGUID
    , @PackageName
    , @TaskGUID
    , @TaskName
    , @ExtractRowCount
    , @InsertRowCount
    , @UpdateRowCount
);
SELECT 0 AS ExtractRowCount
    , 0 AS InsertRowCount
    , 0 AS UpdateRowCount
;

Creating the log event handler in the package

Before we can create this event handler you have to create three variables of type Int32 with value 0 as in this image:

OnPostExecuteVariables

To create the event handler in a package open the Event Handlers tab and select the package in the Executable dropdown box and select the OnPostExecute in the Event handler dropdown box.

Next drop a Execute SQL Task from the toolbox on the canvas and use the following setting in the General tab:

  • Name: SQL Log Event
  • ResultSet: Single row
  • ConnectionType: OLE DB
  • Connection: The name of the connection with your log table
  • SQLSourceType: Direct imput
  • SQLStatement:
    EXEC [meta].[uspLogEvent] ?,?,?,?,?,?,?,?,?,?

 

OnPostExecute1

In the Parameter Mapping tab add 9 mappings using the following variable names:

  • System:: ExecutionInstanceGUID
  • System:: StartTime
  • System:: PackageID
  • System:: VersionGUID
  • System:: PackageName
  • System:: SourceID
  • System:: SourceName
  • User:: RcExtract
  • User:: RcInsert
  • User:: RcUpdate

OnPostExecute2

In the Result Set tab add 3 results as in the image below.

OnPostExecute3

And we are ready with the implementation of the event handler in the SSIS Package.

The Results

After executing a package with one Execute SQL Task and one Data Flow Task you will see three rows in the SsisEventLog table. Two for the tasks and one for the package:

OnPostExecuteResult

OnPostExecuteResult2

Using this method you can now easily troubleshoot any (performance) issues with the package.

Using BIML to implement the event handler

To implement this event handler with BIML 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>
</Variables>

<!--Events-->
<Events>
<Event EventType="OnPostExecute"
       Name="OnPostExecute"
       ConstraintMode="Linear">
<Tasks>
    <ExecuteSQL Name="SQL Log Event"
        ConnectionName="NorthwindSales"
        ResultSet="SingleRow">
        <DirectInput>
EXEC [meta].[uspLogEvent] 'OnPostExecute',?,?,?,?,?,?,?,?,?,?
        </DirectInput>
        <Parameters>
            <Parameter Name="0" Direction="Input"
                DataType="Guid"
                VariableName="System.ExecutionInstanceGUID" />
            <Parameter Name="1" Direction="Input"
                DataType="DateTime"
                VariableName="System.StartTime" />
            <Parameter Name="2" Direction="Input"
                DataType="Guid"
                VariableName="System.PackageID" />
            <Parameter Name="3" Direction="Input"
                DataType="Guid"
                VariableName="System.VersionGUID" />
            <Parameter Name="4" Direction="Input"
                DataType="String"
                VariableName="System.PackageName" />
            <Parameter Name="5" Direction="Input"
                DataType="Guid"
                VariableName="System.SourceID" />
            <Parameter Name="6" Direction="Input"
                DataType="String"
                VariableName="System.SourceName" />
            <Parameter Name="7" Direction="Input"
                DataType="Int32"
                VariableName="User.RcExtract" />
            <Parameter Name="8" Direction="Input"
                DataType="Int32"
                VariableName="User.RcInsert" />
            <Parameter Name="9" Direction="Input"
                DataType="Int32"
                VariableName="User.RcUpdate" />
        </Parameters>
        <Results>
            <Result Name="0"
                VariableName="User.RcExtract" />
            <Result Name="0"
                VariableName="User.RcInsert" />
            <Result Name="0"
                VariableName="User.RcUpdate" />
        </Results>
    </ExecuteSQL>
</Tasks>
</Event>
</Events>

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 2 – Logging Package Execution in the Control Flow
  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