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:
- Logging Task Execution using the OnPostExecute Event
(this post) - Logging Package Execution in the control flow.
- 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:
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] ?,?,?,?,?,?,?,?,?,?
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
In the Result Set tab add 3 results as in the image below.
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:
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.
