Jason McKittrick's Blog

SSIS Logging Framework

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

SSISLogging 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.

Log1

 

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.

Log2

 

Log3

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.

Log4

 

 

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 boundarycase 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)

Log5

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.

Log6

 

Log7

 

Updated Rows is the result set from the actual bulk update task.

Log6

Log9

 

Log10

Finally, the Audit.up_Event_Package_OnCount_CF is called in the logging task following our bulk update.

Log11

Log12

 

Log13

 

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *