SSIS logging methods

Information on currently executing as well as details on executed packages can be obtained by SSIS Loggings. These logging details are useful for a variety of reasons. SSIS packages bug fixing, performance monitoring as well for fine tuning can be given as some common scenarios. Some commonly used methods for these purposes are:

• Using SSIS build-in Reports.
• In-build logging feature of SSIS and querying the logs tables.
• Using Event handlers.
• Custom build queries using SSISDB tables and catalog views.
• Implementing SQL Tasks to log into customized log tables, when necessary (at the beginning and at the end of packages).

Method No.

1. SSIS build-in Reports.

One of the widely used logging feature of SSIS. Reports of validations as well as executions are available for monitoring purposes.

2.  In-build logging feature of SSIS and querying the logs tables.

The following are the steps to be followed for enabling logging feature of SSIS in order to query the logs tables, to achieve package level logging information.

• On the menu, select SSIS (or right click while in the control flow) –> Logging.
• On the Configure SSIS Logs: Select the required provider type and click on Add. Check the name checkbox and provide the connection for the data source under configuration column. Once this is done SSIS will create a table as dbo.sysssislog and a system stored procedure as dbo.sp_ssis_addlogentry in the database that you have provided.
• Select the Details tab and you may select the events on which you required execution details to be logged.

3. Using Event handlers.

This is another option to track package progress. Although this option provides the flexibility of performing different tasks on a particular event apart from logging the information, the downside of this option is that it requires the settings to be done everywhere in all the packages as required. But definitely for a lesser no of packages it’s a minor concern. It’s totally up to the developer to decide based on the requirement and the scenario.

4. Custom build queries using SSISDB tables and catalog views

The SSISDB Catalog is the central when working with SSIS Packages, which stores a list of SSIS related objects as well as tables with operational history of all of the deployed SSIS projects. This also comes in handy when querying the execution process of SSIS packages.

For an example:

catalog.executable_statistics in combination with catalog.executables can be used to identify the longest running objects in a package.

—– top five longest running objects in the package.

USE SSISDB

SELECT TOP 5 ex.[executable_name], es.[execution_duration]

FROM [catalog].[executable_statistics] AS es

LEFT JOIN [catalog].[executables] AS ex

ON es.[execution_id] = e.[execution_id]

WHERE ex.[execution_id] = 2

ORDER BY [execution_duration] DESC

——- A View on package execution event information

CREATE VIEW [dbo].[VWPackageExecutionStaus]
AS
SELECT
om.operation_message_id
, om.operation_id
, op.[object_name] Project
, ev.package_name Package
, om.message_time [Date]
,CASE om.message_type
WHEN 120 THEN ‘package failed’
WHEN 130 THEN ‘Task Failed’ ELSE ‘Package Succeed’
END AS [Package Status]
,CONVERT(DATETIME,op.[start_time]) AS [Start Time]
,CONVERT(DATETIME, op.[end_time]) AS [End_Time]
,DATEDIFF(s, op.start_time, op.end_time) AS [Package Duration]

FROM
[SSISDB].[internal].[operation_messages] AS om
INNER JOIN [SSISDB].[catalog].[event_messages] AS ev ON ev.operation_id = om.operation_id
INNER JOIN [SSISDB].[internal].[operations] AS op ON op.operation_id = ev.operation_id

GO

5.  Apart from SSIS in-build logging tables customized tables for logging purposes also can be build and used within the SSIS packages.

——-Table PackageLogDetails

CREATE TABLE PackageLogDetails

(

[PackageLogDetailsID] [int] IDENTITY(1,1) NOT NULL,

[PackageName] [nvarchar](100) NOT NULL,

[StartTime] [datetime] NOT NULL,

[EndTime] [datetime] NULL,

[Success] [bit] NULL CONSTRAINT [DF_PackageLogDetails_Success]  DEFAULT (0)

)

GO

——- Procedure sp_SPackageLog

CREATE PROCEDURE sp_SPackageLog @PackageName nvarchar (100)

AS

BEGIN

INSERT INTO PackageLogDetails (PackageName, StartTime )

VALUES (@PackageName, GetDate() )

END

SELECT MAX(PackageLogDetailsID) FROM PackageLogDetails WHERE [PackageName] = @PackageName

——- Proc sp_EPackageLog

CREATE PROCEDURE sp_EPackageLog @PackageLogDetailsID INT

AS

BEGIN

UPDATE dbo.PackageLogDetails SET EndTime = GetDate() , Success = 1

WHERE PackageLogDetailsID = @PackageLogDetailsID

END