EOL: DTSxChange reached its end of life date on October 1, 2021. See the Solarwinds End of Life Policy for more information.
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?
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)
Available Reports
Select Reports on the main screen to view reports. The following reports are available:
| Report | Description |
|---|---|
| Execution Control Flow Diagram | A 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 Dashboard | Displays the most common counters in SSIS package executions including:
|
| Package Execution Trend | Displays useful information about the package runtime trends, task runtime errors, and task runtime warning trends. |
| Errors/Warnings | Displays 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:
Use the Recent Executions Detail report to display task level details. |
| Recent Executions Detail | Displays information about package and task execution statuses including:
|
| Running Packages | Displays information about currently running packages and tasks including:
|
| Extract/Load Detail | Displays extraction and loading details about each dataflow in each package execution. The information includes:
|
| Extract/Load Trend | Displays a graphical view of the extraction and load trends over several years, weeks, months, and days. |
| Package Performance History Report | Displays a package level performance graph for the specified interval (months, weeks, and days). |
| Package and Task Performance History Report | Displays 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
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.
 |  |
Reports
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
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
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
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
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
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
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
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
