Auditing

As part of best practice I have kept all the auditing details in a separate database. I have given database name as Audit. There are couple of tables, one for auditing of SSIS package and the other for stored procedure auditing.

SSIS Audit table structure

CREATE TABLE dbo.SSIS_Audit
(
LoadId int NOT NULL,
PackageName varchar(100) NOT NULL,
TaskName varchar(100) NOT NULL,
[Type] varchar(100) NOT NULL,
MachineName varchar(50) NOT NULL,
UserName varchar(50) NOT NULL,
NoOfRowsRead int NOT NULL CONSTRAINT DF_MBA_Audit_NoOfRowsProcessed DEFAULT ((0)),
NoOfRowsInserted int NOT NULL CONSTRAINT DF_MAB_Audit_NoOfRowsInserted DEFAULT ((0)),
NoOfRowsUpdated int NOT NULL CONSTRAINT DF_MAB_Audit_NoOfRowsUpdated DEFAULT ((0)),
StartTime datetime NOT NULL,
PackageDuration int NULL
)
GO

Stored Procedure Audit table structure


CREATE TABLE dbo.Error_Audit
(
ErrorNumber numeric(18, 0) NOT NULL,
ErrorSeverity numeric(18, 0) NOT NULL,
ErrorState numeric(18, 0) NOT NULL,
ErrorProcedure varchar(60) NOT NULL,
ErrorLine numeric(18, 0) NOT NULL,
ErrorMessage varchar(250) NOT NULL,
ErrorDate datetime NOT NULL
)
GO


Within SSIS package I used SQL Expression to achieve auditing. I have used Event Handler wherever possible (On Pre-Execute / On Post-Execute) and sometimes I have used Execute SQL Task to achieve this. I couldn’t use event handler on package itself as it is triggering twice. The reason it triggers twice is “it will fire the event handler scoped to the task AND the event handler scoped to the package. So your event actually gets handled twice”. Therefore I use Execute SQL Task to log audit details at the beginning and end of the package to log auditing details.

Example expression to populate SSIS audit table

"INSERT INTO SSIS_Audit (LoadId,PackageName, TaskName, Type, MachineName, UserName, NoOfRowsRead, NoOfRowsInserted, PackageDuration, StartTime)
VALUES ('"+(DT_STR,50,1252)@[User::LocalLoadId] +"',
'"+ @[System::PackageName] + "', '" + @[System::TaskName] + "',
'Package Completed', '" + @[System::MachineName] + "',
'" + @[System::UserName] + "', '"+(DT_STR,50,1252)@[User::RowsRead] +"','"+(DT_STR,50,1252)@[User::RowsInserted] +"',"+(DT_STR,4,1252)DATEDIFF("ss",@[System::StartTime],getdate()) + ", getdate() )"

When the above expression is evaluated it will display something similar to this as SQLStatement

INSERT INTO SSIS_Audit (LoadId,PackageName, TaskName, Type, MachineName, UserName, NoOfRowsRead, NoOfRowsInserted, PackageDuration, StartTime)
VALUES
( '0','Pkg_Fct_Summary_Initial_Load', 'End SSIS Pkg Audit Log',
'Package Completed', 'BEAST', 'BEAST\Sutha', '0', '0',14, getdate()
)


Within stored procedure I have used the following stored procedure to call if anything fails. This Stored procedure will log it into a table which I have specified above (Error_Audit)

CREATE PROCEDURE dbo.prc_LogErrorInfo
AS
BEGIN TRY
INSERT INTO Error_Audit
VALUES
(
ERROR_NUMBER(),
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_PROCEDURE() ,
ERROR_LINE() ,
ERROR_MESSAGE(),
GETDATE()
)
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
GETDATE() AS ErrorDate

END CATCH

As usual comments welcome.

Thanks
Sutha