DTS xChange Auditing and Monitoring

About SSIS Logging/Auditing and Monitoring Framework

DTS xChange adds rich auditing features in your converted packages using the custom auditing framework developed by SentryOne. The auditing frameworks uses all the native SSIS features, and it can track packages in real-time. The auditing framework comes with predefined reports that give you some valuable information.

You can audit the following information by using the reports provided with the auditing framework:

Note:  Use the Report Viewer application to view auditing data.

  • Audit packages that are currently running, and the tasks that are running inside the packages
  • Historical package execution details for a selected date range (Run time, Errors, Warnings etc.)
  • Errors and Warnings by Task and Package
  • Run time by Task and Package
  • Extracted and Loaded Records and their source and target information (Table/View, SQL Query, File Name, Component Name, Data 
  • Flow Name, Connection String etc)
  • Run time Trends for several days/weeks/years by Package and Task
  • Error/Warning/ Trends by Package and Task
  • Extract/Load Trends by Package and DataFlow
  • Extract/Loads Trend by Data Object (File, Table/View or SQL Query)

Note:  DTS xChange can only apply the auditing framework during the conversion process. To re-apply the auditing framework to converted SSIS packages, or newly created SSIS packages you have to use BI xPress. BI xPress also comes with few additional features other than the auditing framework (notification framework, package validation, and package deployment etc).

How does Auditing Framework work?

DTS xChange Auditing Framework architecture diagram

The following changes can be made to an SSIS package when you apply the auditing framework:

Note:  The changes made to your SSIS package are based on the options you select on the auditing screen. 

  • Add a new connection for the auditing database
  • Add a new configuration for the auditing database
    • This step is only performed if you select Add deployment framework
  • Create package level Event Handlers to capture OnError, OnWarning, OnPreExecute and OnPostExecute Events
  • Add count components inside every dataflow task row after the source adapter and before the target adapter to track the extracted and loaded row count
  • Add variables at the package level scope to store the row count for each dataflow
  • Add variables in the OnPostExecute event handler scope to store certain information about DataFlow source/target (Query, TableName)

DTS xChange Auditing Database objects diagram

Available Reports

DTS xChange Reports Menu

Select Reports on the main screen to view reports. The following reports are available:

ReportDescription
Execution Control Flow DiagramA real-time dashboard of running and completed package executions. You can see the progress of the 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 DashboardDisplays the most common counters in SSIS package executions including:
  • the top 20 slow packages
  • the top 20 fast packages
  • minimum used packages
  • maximum used packages
  • recent data extracts
  • recent data loads
Package Execution TrendDisplays useful information about the package runtime trends, task runtime errors, and task runtime warning trends.
Errors/WarningsDisplays a detailed error/warning report that includes package level errors.

Note:  Package level errors are not listed in any other report.
Recent Executions Summary

Displays information about package execution statuses, including:

  • performance detail
  • variable values
  • error/warning messages
  • row count
  • machine name
  • username

Use the Recent Executions Detail report to display task level details.

Recent Executions DetailDisplays information about package and task execution statuses including:
  • performance detail
  • variable values
  • error/warning messages
  • row count
  • machine name
  • username
Running PackagesDisplays information about currently running packages and tasks including:
  • performance detail
  • variable values
  • error/warning messages
  • row count
  • machine name
  • username
Extract/Load DetailDisplays extraction and loading details about each dataflow in each package execution. The information includes:
  • row count
  • source/target information
  • query
  • file name
Extract/Load TrendDisplays a graphical view of the extraction and load trends over several years, weeks, months, and days.
Package Performance History ReportDisplays a package level performance graph for the specified interval (months, weeks, and days).
Package and Task Performance History ReportDisplays package and task level performance graphs for the specified time interval (years, months, weeks, and days).

Execution ControlFlow Diagram

The Package Execution ControlFlow Diagram is a real-time dashboard of your SSIS package activity. The following features are available in the Execution ControlFlow Diagram:

  • Recent Executions
  • Running Packages
  • SSIS Execution Dashboard
  • Extract/Load Detail

Start monitoring executions in Diagram mode by selecting Execution ControlFlow Diagram from the report drop down list, and then select the  desired report. You can also select Start on the ControlFlow tab to open the Report Viewer.  Display the package diagram from the following reports:

  • Visually monitor SSIS package progress in real-time. 
    • You can monitor local or remote packages as long as they have auditing framework applied.
  • Completed/running task runtime and package runtime
  • Extracted/Loaded row count (applicable to DataFlow)
  • Real-time ForEach Loop iteration value
  • Real-time DataFlow Progress with Rowcount and Runtime for individual component (See DataFlow tab)
  • Real-time variable changes
  • User Account under which package was executed
  • Machine Name  
  • Zoom and Scroll diagram
  • Real-time errors/warning of selected package execution

SSIS Execution Dashboard

DTS xChange SSIS Execution Dashboard

The SSIS Execution Dashboard displays the following information:

  • Running packages (Last number of days/weeks/months)
  • Completed packages (Last number days/weeks/months)
  • Top 20 slowest packages (Last 90 days)
  • Top 20 fastest packages (Last 90 days)
  • Top 20 minimum used packages (Last 90 days)
  • Top 20 maximum used packages (Last 90 days)
  • Data extract summary (Last number days/weeks/months)
  • Data load summary (Last number days/weeks/months)

Note:  You can specify the last number of days/weeks/months by changing the interval parameter.

DTS xChange Execution Dashboard Data Extract Report
DTS xChange Execution Dashboard Data Load Report

Reports

Package Execution Trend Report

DTS xChange Package Execution Trend Report

The Package Execution Trend report displays the following information:

  • Package performance trend in graphical and tabular form
  • Package and Task Performance break down for the specified number of weeks/days for the selected time frame
  • Error/Warning trend analysis graph
  • Execution detail in tabular format including the package name, run time, error/warning count, and row count

Errors/Warnings Report

DTS xChange Error/Warning Report

The Error/Warning report displays the following information:

  • All Packages and Tasks with an error or warning
  • Package and Task start and end times
  • Errors/warning details for package execution between a selected time frame

Extract/Load Detail Report

DTS xChange Extract/Load Detail Report

The Extract/Load Detail report displays the following information:

  • All Dataflow summaries for packages executed between a selected time frame
  • Package and Data Flow Tasks Start and End times
  • Data Flow Source and Target details including: SQL Query, Server or File Name, Database Name and User Name

Extract/Load Trend Report

DTS xChange Extract/Load Trend Report

The Extract/Load Trend report displays the following information:

  • The Extracted and Loaded record count trend for a selected time frame
  • Multiple levels of row count trends at the Package, DataFlow and source/target levels
  • Dataflow related information including: Component Type, Data Object Name, Package Name and others

Running Packages Report

DTS xChange Running Packages Report

The Running Packages Detail report displays the following information:

  • Package and Task Runtimes
  • Errors/Warnings for related tasks
  • Extracted/Loaded rows (applicable to DataFlow)
  • User Account for the executed package
  • Machine Name
  • Interactive or Non-Interactive mode

Recent Executions Report

DTS xChange Recent Executions Report

The Recent Execution Detail report displays the following information:

  • Package and Task Runtimes
  • Errors/Warnings for related tasks
  • Extracted/Loaded rows (applicable to DataFlow)
  • User Account for the executed package
  • Machine Name
  • Interactive or Non-Interactive mode

Package Performance History Report

DTS xChange Package Performance History Report

The Package Performance History report displays the following information:

  • Performance history graph for packages that executed during a selected time frame
  • Error indicator bars for the period when the execution failed
  • Total package executions for a specified time frame
  • Preview performance trend graph
  • Average package runtime for a selected time frame

Package and Task Performance History Report

DTS xChange Package and Task Performance History Report

The Package and Task Performance History report displays the following information:

  • Performance history graph for packages and tasks that executed during a selected time frame
  • Performance history graph for dataflow components
  • Error indicator bars for the period when the execution failed
  • Total package executions for a specified time frame
  • Preview performance trend graph
  • Average package runtime for a selected time frame

DTS xChange Package and Task Performance History Report