How to do custom SSIS Logging three different ways?

In this article you will learn different techniques for SSIS Logging (Integration Services Logging) and Monitoring. There are mainly 3 different ways you can do ssis logging.

SSIS Logging and Reporting Video Tutorial

Download ssis logging sample files used in this article

Download ssis logging tool used in this article

Technique-1: ssis logging using log provider
Technique-2: ssis logging using event handlers
Technique-3: ssis logging using BI xPress Auditing Framework

Before we look at various technique of Custom SSIS Logging and Monitoring. Please consider the following points which can help you to decide which approach you should take.

Technique-1 : ssis logging using log provider

Pros:

No coding required to capture ssis logging data
Simple and clean implementation without messing up with controlflow
It can log start/end time of package and task.
It can log errors and warnings.
Flexibly to turn ON and OFF events you want to log at Package or Task Level

Cons:

Row count cannot be tracked
If you use SQL Server Log Provider then data is logged to system table (sysdtslog90 or sysdtspackagelog) and you have no control on schema and how data is logged
Connection strings cannot be tracked
Variable values cannot be tracked
Custom coding required to find Runtime Interval and other performance detail for package and task execution
Certain options can be confusing to first time user
You have to write custom reports to get meaningful information

How many types of SSIS Log Providers available for native SSIS Logging?

There are five native log providers are available but most common are SQL Server and Text File Log Provider.

SQL Server Log Provider
Text file Log Provider
Windows Event Log Provider
XML File Log Provider
SQL Server Profiler Log Provider

How to implement SSIS Logging using Text File Log Provider?

In this section you will learn how to implement native ssis logging using text file log provider. Perform the following steps to add ssis log provider.

Create a simple SSIS Package. Add 3 dummy script tasks from toolbox and connect them. To make it interesting on last script task we added some fake variable access code ( System.Windows.Form.MessageBox.Show(Dts.variables("varSomeFakeVariable").Value.ToString()) ) this way we can test error.
Right click on SSIS Package designer surface and click Logging option (Or Select from top menu SSIS > Logging)
On the ssis logging provider dialogbox check Package node, add text file log provider, check log provider option to enable it and then click add new connection (see below screenshot)
Enter connection information for log file (see below screenshot)
Once connection is selected for log file. You have to configure events and other data you wish to log. Goto Details tab and check events you wish to log.
Now save package run it. You can open log file to view execution log.
Below is the sample log from native text file provider. As you can see below its not pretty looking report but it will give you some ideal about package execution events and error/warning messages (You may have to search for event "OnError" in text file or sql table).

Technique-2 : custom ssis logging using event handlers

Now lets look at the technique which many people prefer due to the fact it gives you full control how and what you want to log compared to log provider approach but still there are many issues with this techniques (See Pros/Cons Below).

Pros:

You have full control on schema and how data is logged
Row count can be tracked with manual coding
You can track start/end time of package and task.
You can track errors and warnings.

Cons:

Implementation and maintenance can be time consuming if you have lots of packages to audit. If you are tracking row count then it becomes even more clunky and very soon you may realize that its becoming nightmare to maintain it.
Coding is required to capture various data
Connection strings cannot be easily tracked (Requires coding)
Variable value cannot be easily tracked (Requires coding)
Custom coding required to find Runtime Interval and other performance detail for package and task execution
You have to write custom reports to get meaningful information

How to use eventhandlers to capture ssis execution log?

In this section we will look at how to create a simple package which will capture package and task start-time and end-time. For this we will create a simple Table which will store package start and end time along with other information.

To implement this technique for package execution logging we will first create a log database by running the following script (In this example (local) instance with windows authentication is used)

CREATE DATABASE ETL_Log
GO
USE [ETL_Log]
GO
CREATE TABLE [dbo].[PackageLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ExecutionID] uniqueidentifier NOT NULL,
	[PackageName] [nvarchar](255) NOT NULL,
	[PackageID] [varchar](50) NOT NULL,
	[UserName] [nvarchar](100) NULL,
	[MachineName] [nvarchar](255) NULL,
	[StartDateTime] [datetime] NOT NULL,
	[EndDateTime] [datetime] NULL
) 
GO
CREATE TABLE [dbo].[ErrorLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ExecutionID] uniqueidentifier NOT NULL,
	[SourceName] varchar(255) not null,
	[Message] nvarchar(max) not null,
	[MessageCode] int not null,
	[LogDateTime] [datetime] NOT NULL,
) 
GO

Once ETL_Log database is created you can create a simple SSIS Package. Add 3 dummy script tasks from toolbox and connect them. To make it interesting on last script task we added some fake variable access code ( System.Windows.Form.MessageBox.Show(Dts.variables("varSomeFakeVariable").Value.ToString()) ) this way we can test error.
Add OnPreExecute eventhandler: Click on eventhandler tab and select OnPreExecute eventhandler from the eventhandler dropdown. Click in the designer area link to create new eventhandler for PackageLevel Scope (Make sure scope by looking at Executable Dropdown).
Add sequence container and executesql task, attach sequence container with executesql task as below. Right click on precedence constraint and "Edit". Select evaluation method "Expression" and enter the following expression in the value field @[System::SourceID]==@[System::PackageID]
Above expression will filter any activity for tasks and only log when OnPreExecute is fired for Package Start.
Double click on executesql task. Change name to "Log Package Start", select New OLEDB Connection for ETL_Log database in the connection dropdown, in the sqlstatement field enter the following command.
```
INSERT INTO [PackageLog]([ExecutionID],[PackageName],[PackageID],[UserName],[MachineName],[StartDateTime],[EndDateTime]) 
VALUES(?,?,?,?,?,getdate(),NULL)
```
Click on the parameter mappings tab and add parameter mapping as below for each parameter for above sql statement.
Add OnPostExecute eventhandler: Click on eventhandler tab and select OnPostExecute eventhandler from the eventhandler dropdown. Click in the designer area link to create new eventhandler for PackageLevel Scope (Make sure scope by looking at Executable Dropdown).
Add sequence container and executesql task, attach sequence container with executesql task as below. Right click on precedence constraint and "Edit". Select evaluation method "Expression" and enter the following expression in the value field @[System::SourceID]==@[System::PackageID]
Above expression will filter any activity for tasks and only log when OnPostExecute is fired for Package End.
Double click on executesql task. Change name to "Log Package End", select OLEDB Connection for ETL_Log database in the connection dropdown, in the sqlstatement field enter the following command.
```
UPDATE [PackageLog] 
SET [EndDateTime]=getdate() WHERE [ExecutionID]=?
```
Click on the parameter mappings tab and add parameter mapping as below for each parameter for above sql statement.
Add OnError eventhandler: Click on eventhandler tab and select OnError eventhandler from the eventhandler dropdown. Click in the designer area link to create new eventhandler for PackageLevel Scope (Make sure scope by looking at Executable Dropdown).
Add executesql task and rename it to LogError
Double click on executesql task, select OLEDB Connection for ETL_Log database in the connection dropdown, in the sqlstatement field enter the following command.
```
INSERT INTO [ErrorLog]([ExecutionID],[SourceName],[Message],[MessageCode],[LogDateTime])
     VALUES(?,?,?,?,getdate())
```
Click on the parameter mappings tab and add parameter mappings as below for each parameter for above sql statement.
Now save the package and execute it. You can query PackageLog table to see sample log data.
Below is the sample log generated from package eventhandler. As you can see below its more flexible approach compared to Log Provider Method but its not as easy as log provider method.

Technique-3 : ssis logging using BI xPress Auditing Framework

Pros:

Hundreds of packages can be modified in few clicks so they can be fully audited when executed on any machine.
Framework can be easily removed if not needed in few clicks.
Option to watch real-time package execution visually just like BIDS Designer
Row count is tracked for various components in dataflow.
Variable values are logged including every change made to variable value.
Connection strings are logged for all connection manager makes it easy to troubleshoot datasource and conf related issues.
Package and task execution details are logged.
Errors and Warnings are logged.
Some important information about sources and targets are logged ... including connection, sqlstatement, file path etc.
Lots of inbuilt reports to view historical execution information and performance trend for package and tasks.
Framework is implemented using all native SSIS tasks so it makes it very easy to port packages from one machine to another machine (No need to install BIxPress or any dlls on other machine).
BIxPress Auditing Framework is fully tested and implemented by thousands of small and large companies.

Cons:

Not FREE. You have to purchase BI xPress.

SSIS Video Tutorial - Implementing SSIS Logging using BIxPress

How to apply auditing framework using BI xPress?

If you never used BIxPress before then perform the following steps to implement Auditing Framework.

Download and Install BI xPress Free Trial (Fully functional for 14 days)
Once BI xPress is installed launch the BI xPress application from start menu > BI xPress
After application is launched click on the Auditing button to launch auditing framework wizard
Select "Add/Re-Apply Auditing Framework" option and click next
Select packages you want to apply auditing framework and click next
On the connection information tab enter server name and select database you wish to use for BIxPress. Select "NEW" if you want to create new database for package execution log and other information.
Click next when you enter all settings for auditing framework
On the process summary screen Click Start to apply auditing framework
Close the wizard.
Execute any package so we have some auditing data logged.
To see BI xPress logging in action goto BI xPress main screen and click on the Monitoring button to launch Report Viewer application. Report Viewer Application provides real-time and historical execution information. Click on "Start" button and if prompted enter connection information for BIxPress database where log is stored. Once you connected you should see package execution in picture view as below. You can also see static reports by selecting from the top dropdown.

SSIS Logging options for Dataflow

Log Row Count: (ON by default) It allows you to capture row count flowing inside your dataflow. When you enable row count feature each dataflow task is modified to track row count. Row count component is attached with each source and destination..

Log Source Row Count: Check log source row count (ON by default) if you want to track how many rows coming from source component (e.g. Flat File Source, OLEDB Source, Excel Source or any Custom Source Component). Row count will be only logged after dataflow execution is completed. BI xPress will attach one row count transform after each source component in your dataflow to count rows.

Log Destination Row Count: Check log destination row count (ON by default) if you want to track how many rows going to destination component (e.g. Flat File Destination, OLEDB Destination, Excel Destination or any Custom Destination Component). Row count will be only logged after dataflow execution is completed. BI xPress will attach one row count transform before each destination component in your dataflow to count rows.

Log SQL Statement/Data source name (i.e. table, file, stored proc): Check log SQL Statement option if you want to log SQL Statement used to extract the data. It also logs the following information.

- Table Name
- Stored Proc Name
- SQL Statement (Upto 8000 Characters)
- File Name (e.g. excel file, xml file, flat file)

SSIS Logging options for Variables and Connection Logging

Connection Logging: This option allows you to log every connection manager connection string value during runtime. If you are using Configurations to make connections dynamic then its very important to find out whats the value being passed to connectionstring from configuration. Connection List shows connection name, datasource and full connectionstring (Without Password). Connection manager information can be also seen in Execution Summary Report and Execution Detail Report

Figure 3.3: SSIS Logging for Connection Managers.

Figure 3.6: SSIS logging for ADO Recordset.

Log variables when execution is completed: This option allows you to log variable values at the end of package execution (OnPostExecute) event. You can view variable values in Package Execution Reports (See Figure 3.7) or inside controlflow diagram (see Figure 3.8).

Figure 3.7: Variable logging (In recent execution report).

Figure 3.8: Variable logging (In controlflow diagram).

Log Variable Changes in ForEachLoop iterations

This is another Unique feature of BI xPress Auditing Framework. It log variable changes and display Iteration Value of ForEach Loop in real time. See screenshot below.

For example if you looping through files in a folder and loading them at runtime tracking variable changes can give idea how many files loaded so far during execution.

Figure 3.4: ssis variable change history log and foreach loop iteration value.

Track all variable changes: This option will log value of any variable (except object datatype) everytime its modified by any task inside SSIS Package. You can see full change log in realtime inside SSIS Monitoring Console when you enable this option (Default ON).

Track only ForEach iteration variables: Select this option if you only want to see change log for variables involved in ForEachLoop iterations (On the variable mappings tab). This option will log variable changes made by each iteration and also show you current value above ForEachLoop Task inside ControlFlow Diagram in SSIS Monitoring Console.

Enable advanced features: Checking this option will enable the following 2 features
- Track variables with any datatype (including object, numbers, datetime, guid)
- Log full value of variable (more than 4000 chars). If you uncheck advanced logging then only first 4000 characters are logged.

How to launch SSIS Monitoring Console

To open the SSIS Monitoring Console, either click the SSIS Monitoring Console option in the main "BI xPress" menubar of BIDS or you can launch it from standalone application. After opening the SSIS Monitoring Console, you will be able to start realtime monitoring or view historical reports (See Figure 1) from the main screen.

To start monitoring of ssis executions or view report perform the following step.

1. Click on the "Start" link or click on the report link you want to view.
2. If you running this application first time then you will be prompted to enter auditing database connection information (See Figure 2). Provide valid connection information and click view report.

Figure 1: SSIS monitoring console welcome screen.

Report Viewer Options and Connection Information

Figure 2: Connection information and report filters.

Report Selection

SSIS Monitoring Console comes with several out of the box reports. In each of these reports, you will be able to filter data by start and end dates, execution status, package names, machine names, database names, and many others (See Figure 2). Before viewing the report, you will need to enter the server name where the auditing database exists and specify the necessary credentials. From the database drop down menu, select the database containing the auditing tables. When you are ready to view the report, click "View Report".

There are several inbuilt reports are shipped this version and more will be added in the future release.

Report Name	Description
Execution ControlFlow Diagram	This is a real-time dashboard of running/completed package executions. You can visually see progress of package workflow just like you can see while debugging packages in BIDS. Any package with auditing framework applied with BI xPress v2.2 or greater can be monitored in diagram mode.
SSIS Execution Dashboard	This dashboard gives one place view of most common counters of SSIS package execution including top 20 slow packages, fast packages, minimum used packages, maximum used packages, recent data extracts, recent dataloads.
Package Execution Trend	This report provides many useful information about trend of package runtime, task runtime and errors/warnings.
Errors/Warnings	Provides detailed error/warning report including package level errors (package level errors not listed in any other report)
Recent Executions Summary	Provides information of execution status of packages including performance detail, variable values, error/warning messages, rowcount, machine name, username and other useful information. If you want to see task level detail then use Recent Executions Detail Report
Recent Executions Detail	Provides information of execution status of packages/tasks including performance detail, variable values, error/warning messages, rowcount, machine name, username and other useful information.
Running Packages	Provides information of currently running packages and tasks including performance detail, variable values, error/warning messages, rowcount, machine name, username and other useful information.
Extract/Load Detail	Provides extract/load detail (e.g. row count, source/target information, query, file name ...) about each dataflow in each package execution.
Extract/Load Trend	Provides graphical view of extract and load trend over several years, weeks, months and days.
Package Performance History Report	Provides package level performance graph for specified interval (weeks, months and days).
Package and Task Performance History Report	Provides package and task level performance graphs for specified interval (years, weeks, months and days).

Purging auditing records

To delete the auditing records permanently from the selected database, click the Purge History button. Clicking Purge History will open the Purge Audit Records dialogue window (See Figure 3). In this window you can specify which audit records to delete based on machine name, package name, and age. You can also choose to delete all of the audit records. This action could take several minutes to perform depending on how many records exist in the database. When you are ready to delete the specified audit records, click the Delete Records Now button.

Figure 3: Purge Audit Records dialogue window.

Advanced Options

Click here to read more

Package Filtering

Figure 3: Package Filtering.

SSIS Logging and Auditing Database Connection Information

In this section you can specify connection information of the auditing database where package execution log is stored. Once you specify Server, Database and UserID/Password click Connect to start realtime monitoring or view static report.

ControlFlow Tab (Real-time ssis logging and package execution monitoring)

Using BI xPress auditing framework you can monitor currently running or past executions of SSIS packages visually (just like BIDS) in ControlDiagram mode (See Figure-2). ControlFlow diagram can be displayed for single package or multiple packages.

Figure 3: Real-time package execution monitoring (ControlFlow Diagram).

Diagram Panel (SSIS Logging Viewer)

Diagram pane displays control flow of selected package in the execution list (See right side - Block 2). Control flow diagram provides more detail than BIDS designer.

Features:

- Visual representation of each step. Red=Completed with Error , Green=Completed without Error, Yellow=Running, Gray=Disabled, No color=Not started
- Execution time taken by each step.
- Row count is displayed for each completed dataflow. Row count displayed in the "Rows: {extracted rows from source} >> {loaded rows in target}" format. Where extracted rows are total rows coming from each source and "loaded rows" is total of row count for all destination.
- ForEach loop indicator for every iteration in real time. If package is completed then last iteration value is displayed. Iteration value is coming from first variable mapping.

Important Note
Row count is only displayed under dataflow if "Log row count (For DataFlow)" option is turned on (See Logging options tab on the auditing wizard). ForEach Loop iteration indicator is only available if "Enable variable change tracking" option is turned on (See Logging options tab on the auditing wizard).

Execution List Panel (SSIS Logging Viewer)

Execution List panel displays last <N> executions. Execution list provides the following options.

Option	Description
Show last <N> executions	This value indicates how many recent executions you want to display in the list (Most recent at the top).
Refresh every <N> sec	This interval specifies how often you want to refresh execution list. If you feel slow response then increase refresh interval.
Filter	If you want to see only certain packages in the execution list then you can set package filter.
Refresh List	Click this link to refresh list manually.
Delete selected items	You can deleted checked items from execution list. Deleting item also deletes any associated records from all other table in auditing database.

List items are color coded as Red=Completed with Error , Green=Completed without Error, Yellow=Running.

In the execution list you can right click to launch most common reports for the selected package.

Figure 3.1: Launch most common reports for selected package using quick launch menu.

Error/Warning Panel (SSIS Logging)

Error/Warning panel displays errors and warning of selected execution. This panel has 3 options. All of them also available from right click popup menu.

Optionn	Description
Show/Hide Errors	Checking this option lists all errors in the list view and unchecking hides errors.
Show/Hide Warnings	Checking this option lists all warnings in the list view and unchecking hides warnings.
View entire list in full screen	Clicking this option opens new window with all errors in full screen where you can easily copy/print errors/warnings.

Connections Window (SSIS Logging Viewer)

Connection window displays connection name, datasource name and connectionstring for all connections. This feature is only available for auditing framework applied with BI xPress v3.0 or higher. Connection icons are different for each connection type. Connection strings are logged after Configurations are applied during runtime.

Variables Window (SSIS Logging Viewer)

Variable window by default displays value of variables when package execution completed. If you want to view real time changes of variable value then check "Show all changes" option. Variables only displayed if variable tracking option is selected while applying auditing framework (variable logging option is on Logging Option tab on auditing framework wizard)

Diagram Toolbar (SSIS Logging Viewer)

Diagram toolbar provides the following options.

Optionnnn	Description
	Back button will switch view to the previous screen.
	Changing preview mode to other than 1 pkg will switch to Matrix mode. Matrix mode allows to view multiple packages in the same view. In Matrix mode you can view upto 16 packages in live mode.
	Zoom in and Zoom out will
	Zoom to fit option will resize controlflow or dataflow diagram to fit image inside the diagram area so you don't have to scroll to see entire image.
	Full screen option will hide parameter panel.
	Save current diagram image as JPG, PNG, BMP or GIF
	This option will set live mode to ON or OFF. Live mode is by default set to ON. Diagram and Execution List is automatically refreshed to provide real time status when Live Mode is ON then. Note: Live mode is set to OFF when any error is occurred during diagram refresh.
	Show/Hide error list option will hide or show error/warning window at the bottom. Error window provides real-time status of various errors during currently opened package.
	Show/Hide variable list option will hide or show variable window at the bottom. You can check variable values at end of package execution or in realtime when this window is visible. Note: Variable logging must be turned on to see variables in the list.
	Show/Hide execution list option will show or hide right hand side execution list window.

Execution Statusbar (SSIS Logging Viewer)

Execution statusbar provides package name, runtime, package start date, machine name and user account under package was executed. Execution statusbar is set to Yellow, Green or Red depending on package execution status.

Advanced Options (SSIS Logging Viewer)

Figure 3: Advanced Options.

Option	Description
Auto Refresh Reports	Check this option if you want to refresh selected static report automatically at certain interval.
Auto Hide Parameters Panel	Check this option if you want to hide parameter panel when "View Report" button is clicked
Diagram Refresh Interval	This setting defines how often you want to refresh diagram (ControlFlow or DataFlow). If you have slow system and refreshing diagram too often is causing performance issue then increase the interval to reduce the refresh speed.
Execution List Refresh Interval	This interval specifies how often you want to refresh package execution list. If you have slow system and refreshing executionlist too often is causing performance issue then increase the interval to reduce the refresh speed.
Always Expand Container Tasks	When you save packages with containers it will appear same way in the diagram as you save (expanded or collapsed). When container is collapsed it makes it difficult to see execution status of child task. This option will expand container in the diagram regardless of its original status so you can see activity of all tasks inside container.
Hide Iteration Value	This option will hide iteration value above ForEachLopp task. This option is only valid for those packages which have Variable tracking turned on.

SSIS Logging - Multiple package monitoring (Matrix Mode)

To show multiple packages in the controlflow diagram check executions in the execution list. You can check upto 16 executions. Once you check multiple execution you will see Matrix Mode enabled automatically (See Figure 3.1). To open package in the full screen mode click Full Screen icon found in the top-right corner on each package block. You can use "Back" arrow to go back and forth between single package and multi package monitoring.

Figure 3.1: Multi-Package monitoring (ControlFlow Diagram).

SSIS Logging - DataFlow Tab (Real-time dataflow execution monitoring)

DataFlow Tab provides easy way to monitor dataflow progress and debug individual steps inside dataflow. Dataflow UI is divided in 3 sections (See Figure 4).

Figure 4: DataFlow monitoring UI.

SSIS Logging Options - Dataflow Diagram Panel

Diagram pane displays data flow image of the selected dataflow in the list (See left side - Block 2).

Features:

- Visual representation of each step. Red=Completed with Error , Green=Completed without Error, Yellow=Running, Gray=Disabled, No color=Not started
- Execution time taken by each component inside dataflow.
- Row count is displayed for each path. If you are in Live Mode then row count is updated for each buffer passing through pipeline.

SSIS Logging Options - DataFlow List panel

This panel lists all dataflows of selected package. DataFlow tasks are grouped by their status and "Running" tasks are listed at the top. There are 2 different views for dataflow list (See DataFlow List Toolbar icons).

Tile View (Default) : This view shows large icons of dataflow and status is displayed in different color.
Detail View : This view provides compact list of all dataflow icons so you don't have to scroll less compared to Tile view.

SSIS Logging Options - ControlFlow Preview Panel

This panel provides realtime preview of selected package controlflow so you don't have to switch between control flow and dataflow to see status of other steps. If you want to improve performance then you can disable control flow preview by un-checking "Show Live ControlFlow Preview"

Download ssis logging sample files used in this article

Download ssis logging tool used in this article

How to do custom SSIS Logging three different ways?

SSIS Logging and Reporting Video Tutorial

Technique-1 : ssis logging using log provider

How many types of SSIS Log Providers available for native SSIS Logging?

How to implement SSIS Logging using Text File Log Provider?

Technique-2 : custom ssis logging using event handlers

How to use eventhandlers to capture ssis execution log?

Technique-3 : ssis logging using BI xPress Auditing Framework

SSIS Video Tutorial - Implementing SSIS Logging using BIxPress

How to apply auditing framework using BI xPress?

SSIS Logging options for Dataflow

SSIS Logging options for Variables and Connection Logging

Log Variable Changes in ForEachLoop iterations

How to launch SSIS Monitoring Console

Report Viewer Options and Connection Information

Report Selection

Purging auditing records

Advanced Options

Package Filtering

SSIS Logging and Auditing Database Connection Information

ControlFlow Tab (Real-time ssis logging and package execution monitoring)

Diagram Panel (SSIS Logging Viewer)

Execution List Panel (SSIS Logging Viewer)

Error/Warning Panel (SSIS Logging)

Connections Window (SSIS Logging Viewer)

Variables Window (SSIS Logging Viewer)

Diagram Toolbar (SSIS Logging Viewer)

Execution Statusbar (SSIS Logging Viewer)

Advanced Options (SSIS Logging Viewer)

SSIS Logging - Multiple package monitoring (Matrix Mode)

SSIS Logging - DataFlow Tab (Real-time dataflow execution monitoring)

SSIS Logging Options - Dataflow Diagram Panel

SSIS Logging Options - DataFlow List panel

SSIS Logging Options - ControlFlow Preview Panel

Tags

ssis logging | ssis package logging | ssis custom logging | ssis monitor | ssis logging best practices | ssis performance | ssis auditing | ssis report