SSIS Custom Logging vs BIxPress Logging In this article you will learn different techniques for SSIS Logging (Integration Services Logging) and Monitoring. There are mainly 3 different ways you can dossis logging. SSIS Logging and Reporting Video Tutorial Download ssis logging sample files used in this article Download ssis logging tool used in this article
In this article you will learn different techniques for SSIS Logging (Integration Services Logging) and Monitoring. There are mainly 3 different ways you can dossis logging.
Download ssis logging tool used in this article
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.
There are five native log providers are available but most common are SQL Server and 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.
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).
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.
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
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.
UPDATE [PackageLog] SET [EndDateTime]=getdate() WHERE [ExecutionID]=?
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.
If you never used BIxPress before then perform the following steps to implement Auditing Framework.
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)
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).
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.
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.
Figure 2: Connection information and report filters.
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.
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.
Click here to read more
Figure 3: Package Filtering.
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.
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 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.
Execution List panel displays last <N> executions. Execution list provides the following options.
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 displays errors and warning of selected execution. This panel has 3 options. All of them also available from right click popup menu.
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.
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 provides the following options.
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.
Figure 3: Advanced Options.
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).
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.
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.
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.
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
Firstly, what I want to say about this product is excellent works by Pragmatic Works. It’s such a cool and most function able tool/product mainly design for SSIS field. Some of the main features is that it allows us to add auditing, monitoring, and notification to all of our SSIS packages. I mainly found the auditing, monitoring and reporting features of this product as great one. Also ,I suggest anyone in the field of BI to try this product
Regards,
Anil Maharjan