The need for a Logging Framework for SSIS packages to track and log activity within ETL (Extract, Transform, Load) processes. This blog outlines the database schema, stored procedures and implementation practices within SSIS packages to be utilized for the SSIS packages used for the data applications. I will build and use a sample package load of Regions and Sub Regions for illustrative purposes.
Logging Schema
Note: See the end of the blog for the SQL CMD Build Script for the LoggingDB
ExecutionLog
The ExecutionLog table is used to store high level SSIS event metrics. This table is loaded by executiing the Audit.up_Event_Package_OnBegin procedure in the SSISLogging database. This will be executed through the ExecuteSQL Task within SSIS. As seen in the figure below, the ExecutionLog procedure is called within the Control Flow of the SSIS package. This will marks the beginning of a process within the logged SSIS execution.
In order to capture the related package information the following parameters are passed to the procedure from the package and user variables for the package. If you wanted to add addition telemetry this is where you would setup the Variable Containers. A DB Schema update may also be necessary if you add additional telemetry.
To complete the logging of the individual Control Flow task (Load SubRegions DataFlow Task in the example) the Audit.up_Event_Package_OnEnd procedure is called.
StatisticLog
The StatisticLog table is used to store more granular component metrics. There are two common methods for creating these metrics: 1) within a Data Flow and 2) as a result of a Control Flow task.
Within the Load SubRegions Data Flow task, we want to capture metrics such as Rows Affected and the Time associated with processing those rows. This is done through a call to the Audit.up_Event_Package_OnCount procedure from within a Script Task within the Data Flow.
The script task utilizes the following code:
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Public Class ScriptMain
Inherits UserComponent
Private startTicks, totalTicks As Long
Private rowCount, totalRows As Integer
Private rps As New ArrayList() ‘rps = rows per second
Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
‘Save the rate statistic for this buffer
If startTicks <> 0 Then
totalRows += rowCount
Dim ticks As Long = CLng(DateTime.Now.Ticks – startTicks)
If ticks > 0 Then
totalTicks += ticks
Dim rate As Integer = CInt(rowCount * (TimeSpan.TicksPerSecond / ticks))
rps.Add(rate)
End If
End If
‘Reinitialize the counters
rowCount = 0
startTicks = DateTime.Now.Ticks
‘Call the base method
MyBase.Input0_ProcessInput(Buffer)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
rowCount += 1 ‘No exposed Buffer.RowCount property so have to count manually
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()
‘Define the Stored Procedure object
With New OleDbCommand(“audit.up_Event_Package_OnCount”)
.CommandType = CommandType.StoredProcedure
‘Define the common parameters
.Parameters.Add(“@logID”, OleDbType.Integer).Value = Variables.LogID
.Parameters.Add(“@componentName”, OleDbType.VarChar, 50).Value = Me.ComponentMetaData.Name
.Parameters.Add(“@rows”, OleDbType.Integer).Value = totalRows
.Parameters.Add(“@timeMS”, OleDbType.Integer).Value = CInt(totalTicks \ TimeSpan.TicksPerMillisecond)
‘Only write the extended stats if RowCount > 0
If rps.Count > 0 Then
‘Calculations depend on sorted array
rps.Sort()
‘Remove boundary–case statistics
If rps.Count >= 3 Then rps.RemoveAt(0)
‘Calculate min & max
Dim min As Integer = CInt(rps.Item(0))
Dim max As Integer = CInt(rps.Item(rps.Count – 1))
‘Define the statistical parameters
.Parameters.Add(“@minRowsPerSec”, OleDbType.Integer).Value = min
.Parameters.Add(“@maxRowsPerSec”, OleDbType.Integer).Value = max
End If
‘Define and open the database connection
.Connection = New OleDbConnection(Connections.SSISLogging.ConnectionString)
.Connection.Open()
Try
.ExecuteNonQuery() ‘Execute the procedure
Finally ‘Always finalize expensive objects
.Connection.Close()
.Connection.Dispose()
End Try
End With
End Sub
End Class
This code will capture the affected rows and time by monitoring the input buffer of the DTSPipeline related to the Data Flow tasks metrics required.
When metrics are required as a result of an Execute SQL Task within the Control Flow, a different method/procedure is used. (Audit.up_Event_Package_OnCount_CF)
Two additional variables are added within the package: vUpdateTableStartDate and vUpdatedRows.
The UpdateTableStartDate is populated from the result set of the step prior to our bulk update operation.
Updated Rows is the result set from the actual bulk update task.
Finally, the Audit.up_Event_Package_OnCount_CF is called in the logging task following our bulk update.
4.1 SQL CMD SSIS Logging Build Script
Orage Text will need updated dated prior to execution.
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName “SSISLogging”
:setvar PrimaryFilePhysicalName “Y:\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\SSISLoggingData1.mdf”
:setvar PhysicalFileName “Y:\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\SSISLoggingData2.ndf”
:setvar PhysicalFileName1 “Y:\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\SSISLoggingData3.ndf”
:setvar PhysicalFileName2 “Y:\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\SSISLoggingData4.ndf”
:setvar PrimaryLogFilePhysicalName “Y:\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\SSISLogging_log.ldf”
USE [master]
GO
:on error exit
IF (DB_ID(N’$(DatabaseName)’) IS NOT NULL
AND DATABASEPROPERTYEX(N’$(DatabaseName)’,‘Status’) <> N’ONLINE’)
BEGIN
RAISERROR(N’The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.’, 16, 127,N’$(DatabaseName)’) WITH NOWAIT
RETURN
END
GO
:on error exit
IF (DB_ID(N’$(DatabaseName)’) IS NOT NULL)
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DatabaseName)];
END
GO
CREATE DATABASE [$(DatabaseName)] ON ( NAME = N’PrimaryFileName’, FILENAME = N’$(PrimaryFilePhysicalName)’) LOG ON ( NAME = N’PrimaryLogFileName’, FILENAME = N’$(PrimaryLogFilePhysicalName)’) COLLATE SQL_Latin1_General_CP1255_CI_AS
GO
:on error resume
EXEC sp_dbcmptlevel N’$(DatabaseName)’, 90
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N’$(DatabaseName)’)
ALTER DATABASE [$(DatabaseName)] SET
ALLOW_SNAPSHOT_ISOLATION OFF
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N’$(DatabaseName)’)
ALTER DATABASE [$(DatabaseName)] SET
READ_COMMITTED_SNAPSHOT OFF
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N’$(DatabaseName)’)
ALTER DATABASE [$(DatabaseName)] SET
MULTI_USER,
CURSOR_CLOSE_ON_COMMIT OFF,
CURSOR_DEFAULT LOCAL,
AUTO_CLOSE OFF,
AUTO_CREATE_STATISTICS ON,
AUTO_SHRINK OFF,
AUTO_UPDATE_STATISTICS ON,
AUTO_UPDATE_STATISTICS_ASYNC ON,
ANSI_NULL_DEFAULT ON,
ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
NUMERIC_ROUNDABORT OFF,
QUOTED_IDENTIFIER ON,
RECURSIVE_TRIGGERS OFF,
RECOVERY SIMPLE,
PAGE_VERIFY NONE,
DISABLE_BROKER,
PARAMETERIZATION SIMPLE
WITH ROLLBACK IMMEDIATE
GO
IF IS_SRVROLEMEMBER (‘sysadmin’) = 1
BEGIN
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N’$(DatabaseName)’)
EXEC sp_executesql N’
ALTER DATABASE [$(DatabaseName)] SET
DB_CHAINING OFF,
TRUSTWORTHY OFF’
END
ELSE
BEGIN
RAISERROR(N’Unable to modify the database settings for DB_CHAINING or TRUSTWORTHY. You must be a SysAdmin in order to apply these settings.’,0,1)
END
GO
USE [$(DatabaseName)]
GO
PRINT N’Adding File FileName2′
GO
:on error exit
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME = [FileName2]
, FILENAME = N’$(PhysicalFileName)’
) TO FILEGROUP [PRIMARY]
GO
PRINT N’Adding File FileName3′
GO
:on error exit
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME = [FileName3]
, FILENAME = N’$(PhysicalFileName1)’
) TO FILEGROUP [PRIMARY]
GO
PRINT N’Adding File FileName4′
GO
:on error exit
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME = [FileName4]
, FILENAME = N’$(PhysicalFileName2)’
) TO FILEGROUP [PRIMARY]
GO
:on error resume
GO
CREATE USER [SSISLogger] FOR LOGIN [SSISLogger] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [SSISLogger]
GO
PRINT N’Creating schemata’
GO
CREATE SCHEMA [Audit]
AUTHORIZATION [dbo]
GO
CREATE SCHEMA [Partition]
AUTHORIZATION [dbo]
GO
CREATE SCHEMA [Reference]
AUTHORIZATION [dbo]
GO
PRINT N’Creating [Reference].[AccountStatus]’
GO
CREATE TABLE [Reference].[AccountStatus]
(
[AccountStatusID] [int] NOT NULL IDENTITY(1, 1),
[AccountStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModifiedDate] [smalldatetime] NULL CONSTRAINT [DF_AccountStatus_LastModifiedDate] DEFAULT (getdate()),
[IsActiveFlag] [bit] NOT NULL
) ON [PRIMARY]
GO
PRINT N’Creating primary key [XPKAccountStatus] on [Reference].[AccountStatus]’
GO
ALTER TABLE [Reference].[AccountStatus] ADD CONSTRAINT [XPKAccountStatus] PRIMARY KEY CLUSTERED ([AccountStatusID]) ON [PRIMARY]
GO
PRINT N’Creating [Audit].[ExecutionLog]’
GO
CREATE TABLE [Audit].[ExecutionLog]
(
[LogID] [int] NOT NULL IDENTITY(1, 1),
[ParentLogID] [int] NULL,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PackageName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PackageGuid] [uniqueidentifier] NOT NULL,
[MachineName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExecutionGuid] [uniqueidentifier] NOT NULL,
[LogicalDate] [datetime] NOT NULL,
[Operator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NULL,
[Status] [tinyint] NOT NULL,
[FailureTask] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
PRINT N’Creating primary key [PK_ExecutionLog] on [Audit].[ExecutionLog]’
GO
ALTER TABLE [Audit].[ExecutionLog] ADD CONSTRAINT [PK_ExecutionLog] PRIMARY KEY CLUSTERED ([LogID]) ON [PRIMARY]
GO
PRINT N’Creating [Audit].[up_Event_Package_OnError]’
GO
CREATE procedure [Audit].[up_Event_Package_OnError]
@logID int
,@message varchar(64) = null –optional, for custom failures
with execute as caller
as
/**********************************************************************************************
Procedure Name:[Audit].[up_Event_Package_OnError]
Description: This stored procedure logs an error entry in the custom event-log table.
Status = 0: Running (Incomplete)
Status = 1: Complete
Status = 2: Failed
Parameters : @ParmaterName ParameterDataType Description
Example:
exec audit.up_Event_Package_OnError 1, ‘Failed’
select * from audit.ExecutionLog where LogID = 1
**********************************************************************************************/
begin
set nocount on
declare
@failureTask varchar(64)
,@packageName varchar(64)
,@executionGuid uniqueidentifier
if @message is null begin
select
@packageName = upper(PackageName)
,@executionGuid = ExecutionGuid
from audit.ExecutionLog
where LogID = @logID
select top 1 @failureTask = source
from dbo.sysdtslog90
where executionid = @executionGuid
and (upper(event) = ‘ONERROR’)
and upper(source) <> @packageName
order by endtime desc
end else begin
set @failureTask = @message
end
update audit.ExecutionLog set
EndTime = getdate()
,Status = 2 –Failed
,FailureTask = @failureTask
where
LogID = @logID
set nocount off
end
GO
PRINT N’Creating [Audit].[up_Event_Package_OnBegin]’
GO
CREATE procedure [Audit].[up_Event_Package_OnBegin]
@ParentLogID int
,@Description varchar(50) = null
,@PackageName varchar(50)
,@PackageGuid uniqueidentifier
,@MachineName varchar(50)
,@ExecutionGuid uniqueidentifier
,@logicalDate datetime
,@operator varchar(30)
,@logID int = null output
with execute as caller
as
/**********************************************************************************************
Procedure Name:audit.up_Event_Package_OnBegin
Description: This stored procedure logs a starting event to the custom event-log table
Parameters :
@ParentLogID int
,@Description varchar(50) = null
,@PackageName varchar(50)
,@PackageGuid uniqueidentifier
,@MachineName varchar(50)
,@ExecutionGuid uniqueidentifier
,@logicalDate datetime
,@operator varchar(30)
,@logID int = null output
Example:
declare @logID int
exec audit.up_Event_Package_OnBegin
0, ‘Description’
,’PackageName’ ,’00000000-0000-0000-0000-000000000000′
,’MachineName’, ‘00000000-0000-0000-0000-000000000000′
,’2008-01-01’, null, @logID output
select * from audit.ExecutionLog where LogID = @logID
**********************************************************************************************/
begin
set nocount on
–Coalesce @logicalDate
set @logicalDate = isnull(@logicalDate, getdate())
–Coalesce @operator
set @operator = nullif(ltrim(rtrim(@operator)), ”)
set @operator = isnull(@operator, suser_sname())
–Root-level nodes should have a null parent
if @ParentLogID <= 0 set @ParentLogID = null
–Root-level nodes should not have a null Description
set @Description = nullif(ltrim(rtrim(@Description)), ”)
if @Description is null and @ParentLogID is null set @Description = @PackageName
–Insert the log record
insert into audit.ExecutionLog(
ParentLogID
,Description
,PackageName
,PackageGuid
,MachineName
,ExecutionGuid
,LogicalDate
,Operator
,StartTime
,EndTime
,Status
,FailureTask
) values (
@ParentLogID
,@Description
,@PackageName
,@PackageGuid
,@MachineName
,@ExecutionGuid
,@logicalDate
,@operator
,getdate() –Note: This should NOT be @logicalDate
,null
,0 –InProcess
,null
)
set @logID = scope_identity()
set nocount off
end –proc
GO
PRINT N’Creating [Audit].[uf_GetExecutionLogRoot]’
GO
CREATE function [Audit].[uf_GetExecutionLogRoot](@logID int)
returns int
with returns null on null input,
execute as caller
as
/**********************************************************************************************
Function Name: Audit.uf_GetDatabaseIsPartitioned
Description: This function returns the root of the execution log tree that the specified
node belongs to
Parameters :
Example:
select audit.uf_GetExecutionLogRoot(3)
**********************************************************************************************/
begin
declare @rootID int
–Derive result using a CTE as the table is self-referencing
;with graph as (
–select the anchor (specified) node
select LogID, ParentLogID from audit.ExecutionLog where LogID = @logID
union all
–select the parent node
select node.LogID, node.ParentLogID from audit.ExecutionLog as node
inner join graph as leaf on (node.LogID = leaf.ParentLogID)
)
select @rootID = LogID from graph where ParentLogID is null
–Return result
return isnull(@rootID, @logID)
end –function
GO
PRINT N’Creating [Audit].[up_Event_Package_OnEnd]’
GO
CREATE procedure [Audit].[up_Event_Package_OnEnd]
@logID int
with execute as caller
as
/**********************************************************************************************
Procedure Name:[Audit].[up_Event_Package_OnEnd]
Description: This stored procedure updates an existing entry in the custom event-log table. It flags the
execution run as complete.
Status = 0: Running (Incomplete)
Status = 1: Complete
Status = 2: Failed
Parameters : @ParmaterName ParameterDataType Description
Example:
declare @logID int
set @logID = 0
exec audit.up_Event_Package_OnEnd @logID
select * from audit.ExecutionLog where LogID = @logID
**********************************************************************************************/
begin
set nocount on
update audit.ExecutionLog set
EndTime = getdate() –Note: This should NOT be @logicalDate
,Status = case
when Status = 0 then 1 –Complete
else Status
end –case
where
LogID = @logID
set nocount off
end –proc
GO
PRINT N’Creating [Audit].[ProcessLog]’
GO
CREATE TABLE [Audit].[ProcessLog]
(
[LogID] [int] NOT NULL,
[RootTableName] [sys].[sysname] NOT NULL,
[PartitionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
PRINT N’Creating [Audit].[uf_GetExecutionLogTree]’
GO
CREATE function [Audit].[uf_GetExecutionLogTree](
@logID int
,@fromRoot bit = 0
)
returns table
–with execute as caller
as
/**********************************************************************************************
Function Name: Audit.uf_GetExecutionLogTree
Description: This function returns the execution log tree that the specified node belongs to,
either the subtree starting from the node, or the whole tree from the root.
Parameters : @logID int
,@fromRoot bit = 0
Example:
select * from audit.ExecutionLog order by LogID desc
select * from audit.uf_GetExecutionLogTree(3, 1)
**********************************************************************************************/
return
(
–Derive result using a CTE as the table is self-referencing
with graph as (
–select the anchor (specified) node
select *, 0 as Depth from Audit.ExecutionLog
where LogID = case @fromRoot
when 1 then Audit.uf_GetExecutionLogRoot(@logID)
else @logID
end –case
–select the child nodes
union all
select leaf.*, Depth + 1 from Audit.ExecutionLog as leaf
inner join graph as node on (node.LogID = leaf.ParentLogID)
)
select
*
,datediff(ss, StartTime, EndTime) as Seconds
from graph
) –function
GO
PRINT N’Creating [Partition].[SubRegion]’
GO
CREATE TABLE [Partition].[SubRegion]
(
[SubRegionID] [tinyint] NOT NULL IDENTITY(1, 1),
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModifiedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_SubRegion_LastModifiedDate] DEFAULT (getdate()),
[IsActiveFlag] [bit] NOT NULL
) ON [PRIMARY]
GO
PRINT N’Creating primary key [XPKSubRegion] on [Partition].[SubRegion]’
GO
ALTER TABLE [Partition].[SubRegion] ADD CONSTRAINT [XPKSubRegion] PRIMARY KEY CLUSTERED ([SubRegionID]) ON [PRIMARY]
GO
PRINT N’Creating [Audit].[StatisticLog]’
GO
CREATE TABLE [Audit].[StatisticLog]
(
[LogID] [int] NOT NULL,
[ComponentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Rows] [int] NULL,
[TimeMS] [int] NULL,
[MinRowsPerSec] [int] NULL,
[MeanRowsPerSec] AS (case when isnull([TimeMS],(0))=(0) then NULL else CONVERT([int],([Rows]*(1000.0))/[TimeMS],(0)) end) PERSISTED,
[MaxRowsPerSec] [int] NULL,
[LogTime] [datetime] NULL CONSTRAINT [DF_ETL_StatisticLog_LogTime] DEFAULT (getdate())
) ON [PRIMARY]
GO
PRINT N’Creating [Audit].[up_Event_Package_OnCount_CF]’
GO
create procedure [Audit].[up_Event_Package_OnCount_CF]
@logID int
,@ComponentName varchar(50)
,@Rows int
,@StartTime datetime
with execute as caller
as
/**********************************************************************************************
Procedure Name:Audit.up_Event_Package_OnCount_CF
Description: This stored procedure logs an entry to the custom RowCount-log table.
Parameters : @ParmaterName ParameterDataType Description
Example:
exec audit.up_Event_Package_OnCount_CF 0, ‘Test’, 100, ‘2008-11-04’
select * from audit.StatisticLog where LogID = 0
**********************************************************************************************/
begin
set nocount on
–Insert the record
insert into audit.StatisticLog(
LogID, ComponentName, Rows, TimeMS
) values (
isnull(@logID, 0), @ComponentName, @Rows, datediff(ms,@StartTime,getdate())
)
set nocount off
end
GO
PRINT N’Creating [Audit].[CommandLog]’
GO
CREATE TABLE [Audit].[CommandLog]
(
[LogID] [int] NOT NULL,
[CommandID] [int] NOT NULL IDENTITY(1, 1),
[Key] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LogTime] [datetime] NULL CONSTRAINT [DF_ETL_CommandLog_LogTime] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N’Creating [Audit].[uf_Progress]’
GO
create function [audit].[uf_Progress]()
returns table
–with execute as caller
as
— select * from audit.uf_Progress()
return(
with cte as (
select
f.LogID
,f.ParentLogID
,space(f.depth * 4) + f.PackageName as ‘PackageName’
,convert(varchar(10), f.LogicalDate, 102) as ‘LogicalDate’
,f.StartTime
,f.EndTime
,f.Seconds
,convert(varchar, f.Seconds / 60) + ‘:’ + right(’00’ + convert(varchar, f.Seconds % 60), 2) as ‘Time’
,case f.Status
when 1 then ‘OK’
when 2 then ‘Failed’
else ‘Processing’
end as ‘Status’
from Audit.ExecutionLog t
cross apply Audit.uf_GetExecutionLogTree(t.LogID, 0) f
where t.ParentLogID is null
)
select top (100) percent
c.LogID
,c.ParentLogID
,c.PackageName
,c.LogicalDate
,c.StartTime
,c.EndTime
,c.Status
— ,c.FailureTask
,s.Rows
,c.[Time]
— ,s.TimeMS
,case when nullif(c.seconds, 0) is null then null else s.Rows / c.seconds end as ‘OverallRps’
,s.MinRowsPerSec as MinRps
,s.MeanRowsPerSec as MeanRps
,s.MaxRowsPerSec as MaxRps
from
cte c
left join
audit.StatisticLog s on c.LogID = s.LogID and s.ComponentName = ‘STAT Source’
order by
c.LogID
)
GO
PRINT N’Creating [Audit].[up_Event_Package_OnCount]’
GO
CREATE procedure [Audit].[up_Event_Package_OnCount]
@logID int
,@ComponentName varchar(50)
,@Rows int
,@TimeMS int
,@MinRowsPerSec int = null
,@MaxRowsPerSec int = null
with execute as caller
as
/**********************************************************************************************
Procedure Name: audit.up_Event_Package_OnCount
Description: This stored procedure logs an error entry in the custom event-log table.
Status = 0: Running (Incomplete)
Status = 1: Complete
Status = 2: Failed
Parameters:
@logID int
,@ComponentName varchar(50)
,@Rows int
,@TimeMS int
,@MinRowsPerSec int = null
,@MaxRowsPerSec int = null
Example:
exec audit.up_Event_Package_OnCount 0, ‘Test’, 100, 1000, 5, 50
select * from audit.StatisticLog where LogID = 0
**********************************************************************************************/
begin
set nocount on
–Insert the record
insert into audit.StatisticLog(
LogID, ComponentName, Rows, TimeMS, MinRowsPerSec, MaxRowsPerSec
) values (
isnull(@logID, 0), @ComponentName, @Rows, @TimeMS, @MinRowsPerSec, @MaxRowsPerSec
)
set nocount off
end –proc
GO
PRINT N’Creating [Audit].[up_Event_Package_OnCommand]’
GO
CREATE procedure [Audit].[up_Event_Package_OnCommand]
@Key varchar(50)
,@Type varchar(50)
,@Value varchar(max)
,@logID int
with execute as caller
as
/**********************************************************************************************
Procedure Name: audit.up_Event_Package_OnCount
Description: This stored procedure logs a command entry in the custom event-log table. A command is termed
as any large SQL or XMLA statement that the ETL performs. It is useful for debugging purposes to know
the exact text of the statement.
Parameters:
@Key varchar(50)
,@Type varchar(50)
,@Value varchar(max)
,@logID int
Example:
exec audit.up_Event_Package_OnCommand ‘Create Table’, ‘SQL’, ‘…sql code…’, 0
select * from audit.CommandLog where LogID = 0
**********************************************************************************************/
begin
set nocount on
–Insert the log record
insert into audit.CommandLog(
LogID
,[Key]
,[Type]
,[Value]
) values (
isnull(@logID, 0)
,@Key
,@Type
,@Value
)
set nocount off
end –proc
GO
PRINT N’Creating [Audit].[up_ClearLogs]’
GO
create procedure [audit].[up_ClearLogs]
with execute as caller
as
begin
set nocount on
truncate table audit.CommandLog
truncate table audit.ExecutionLog
truncate table audit.ProcessLog
truncate table audit.StatisticLog
truncate table dbo.sysdtslog90
set nocount off
end –proc
GO
GRANT CONNECT TO [SSISLogger]
GO