Title: Error Handling and Notification in Microsoft SQL SSIS:

Version: 1.0.0119.01 – January 31, 2019.

Objective: Error log, reporting and email notification in Microsoft SQL.

Article Body: Recently, I had a business requirement to develop Error log, reporting and email notification in Microsoft SQL. The majority of my developments are in SQL Server Integration Services (SSIS) using Visual Studio (VS) Integrated Development Environment (IDE). In this case, SQL 2014 and VS2017.

Thanks to Microsoft SQL, SSIS and VS technologies, we have powerful tools to track runtime errors and email notification. There are multiple methods to implement runtime exception log, errors and email notification. The Event Handlers in SSIS are powerful solution.

1-      After you have created the SSIS Control Flow. In my case, incremental ETL between Production and Report server, select “Event Handlers” TAB.

2-      Select the “Request-New Records” in “Executable” and your desired error-handling module. Select “OnError” for “Event handler”.

3-      Drag and drop “Execute SQL Task” from SSIS Toolbox to Event Handlers TAB canvas. Open the “Execute SQL Task” and navigate to “Parameter Mapping”. Select Add and open the Variable Name drop down. There are large number of System Valuables available to us to log.

4-      In our case, I limited the System Valuables log. In the SQL server, create a log file.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ATICPEDPSYSEVENTLOG](

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

      [SourceID] [nvarchar](max) NULL,

      [PackageName] [nvarchar](max) NULL,

      [SourceName] [nvarchar](max) NULL,

      [ErrorDescription] [nvarchar](max) NULL,

      [CreatedDate] [datetime] NULL,

PRIMARY KEY CLUSTERED

(

      [LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ATICPEDPSYSEVENTLOG] ADD  DEFAULT (getdate()) FOR [CreatedDate]

GO

5-      Cancel and reopen “Execute SQL Task” in Event Handlers TAB canvas. In the “General” TAB, Connect to your database, and past the following in the SQL Statement.

INSERT INTO [ATICPEDPSYSEVENTLOG] (SourceId, PackageName, SourceName, ErrorDescription)

VALUES (?, ?, ?, ?);

6-      Navigate to Parameters Mapping and Add System Source ID, Package Name, Source Name, and Error Description.

You are done.

<Hints>

-          For any other Evet Handlers, copy and paste the “Execute SQL Task” in Event Handlers TAB canvas for your desired SQL module.

-          After the SSIS package deployment in SQL Server, use standard SQL job scheduling and email notification.  For more deployment, instruction refer to “Microsoft Visual Studio, Dynamics AX ETL SSIS Package Deployment, SQL Security”.

-          The SQL Event Log, in my case, ATICPEDPSYSEVENTLOG, contains details log information for subsequent analysis.

If you enjoy what you do; you never work a day in your life.

Cheers, Toraj

References:

·         SSIS Event Handlers Basics, https://www.red-gate.com/simple-talk/sql/ssis/ssis-event-handlers-basics/

·         Event Handlers Tab, https://docs.microsoft.com/en-us/sql/integration-services/event-handlers-tab?view=sql-server-2014