Custom auditing and error handling solution in SSIS

Errors can occur at any stage when you extract data from source and load it into destination. You must include error handling method for your package to test and troubleshoot. There are several ways to handle it at different levels during your process. Either you can use default features provided by SSIS or you can develop your own method to handle the error.

Ex: at a control flow level, if an error occurs, you can add a failure constraint and redirects the workflow to an alternate task. In data flow, if an error occurs, you can send the row out to an error path and save it to review later. There is also an event handling capabilities that let you add OnError events in a separate window. You can either write custom script or just send an email to a team to notify the error.

Here, I am not talking about default features provided by SSIS to handle the error. Let's say, you are developing the package and an error occurs, you should know where to look to identify what happened during the process. you should go to the execution result tab in the designer which shows the package executions details including any warnings or errors.

Let's create a custom tables for auditing and error handling in sql server database. some people think this is a time consuming while SSIS provides many good features for auditing and error handling, but once you develop your own method that gives you detailed information regarding each error that occurs and many more then you will know how much time it saves later to solve the error quickly anytime.

First of all, let's create a table for error log in sql server database

CREATE TABLE [dbo].[ErrorLog]

(

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

[LogTime] [datetime] NULL,

[PackageID] [sysname] NULL,

[PackageName] [sysname] NULL,

[TaskName] [varchar](100) NULL,

[MachineName] [varchar](50) NULL,

[UserName] [varchar](50) NULL,

[ErrorNumber] [int] NULL,

[ErrorDescription] [varchar](max) NULL,

PRIMARY KEY CLUSTERED ([ErrorLogID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO

Now create a store proc:

Next, go to the event handler tab and use the execute sql task to insert the value in errorlog table to need to execute store proc by defining necessary parameters in execute sql task.

CREATE PROCEDURE [dbo].[ups_SSIS_ErrorLog]

@PackageID SYSNAME

, @PackageName SYSNAME

, @TaskName VARCHAR (100)

, @MachineName VARCHAR (50)

, @UserName VARCHAR (50)

, @ErrorNumber INT

, @ErrorDescription VARCHAR(MAX)

AS

DECLARE @LogTime DATETIME

SET @LogTime = GETDATE()

SET @PackageID = UPPER(REPLACE(REPLACE(@PackageID, '{', ''), '}', ''))

INSERT [dbo].[SSIS_ErrorLog]

( [LogTime]

, [PackageID]

, [PackageName]

, [TaskName]

, [MachineName]

, [UserName]

, [ErrorNumber]

, [ErrorDescription] )

VALUES

( @LogTime

, @PackageID

, @PackageName

, @TaskName

, @MachineName

, @UserName

, @ErrorNumber

, @ErrorDescription )

Drag the execute sql task fromt the toolbox and in sql statement write: exec dbo.usp_errorlog ?,?,?,?,?,?,?

Audit: Create an Audit table in your DB:

CREATE TABLE [dbo].[SSIS_Audit](

[LoadId] [int] IDENTITY(1,1) 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,

[NoOfRowsInserted] [int] NOT NULL,

[StartTime] [datetime] NOT NULL,

[PackageDurationinSec] [int] NULL,

CONSTRAINT [PK_SSIS_Audit] PRIMARY KEY CLUSTERED

[LoadId] ASC

)

)

GO

2) Declare the necessary variables in your package

3) Drag the execute sql task in your package, go to the expression and use sql statementsurce: paste following expression there:

"INSERT INTO SSIS_Audit

(PackageName,

TaskName,

Type,

MachineName,

UserName,

NoOfRowsRead,

NoOfRowsInserted,

PackageDurationinSec,

StartTime)

VALUES

('"+ @[System::PackageName]

+ "', '"

+ @[System::TaskName]

+ "','Package Completed', '"

+ @[System::MachineName]

+ "','"

+ @[System::UserName]

+ "', '"

+(DT_STR,50,1252)@[User::RowsRead]

+"','"

+(DT_STR,50,1252)@[User::RowsInserted]

+"','"

+(DT_STR,50,1252)DATEDIFF("ss",@[System::StartTime],getdate())

+ "', getdate()

)"

(Note: I'll add few screenshots later on)

Thanks!