Logging and Alerting for SSIS Package Execution
March 1st, 2012 / No Comments » / by admin
This post will deal with setting up a simple SSIS package logging to enable the potential issues tracking (warnings, errors, execution statuses etc.) from SQL Server agent job via e-mail message. As with everything in IT/BI, there is more than one way to skin the cat and this procedure may not be applicable to your environment or may require small changes to suit you and your set-up. However, it is really easy to follow and build so at any stage you may want to add something to it to increase functionality, don’t hesitate to jump in, experiment and alter things around.
What I want to achieve through this process is to be able to schedule an SSIS job via SQL Agent service and if the job fails, receive notification via e-mail with detailed information on when, how and why the process failed to execute as expected. Sounds like something you could achieve using Precedence Constraint and Send Mail task, but going that way will only give you limited amount of options to play with. First of all, you will not be able to create detailed reports or get rich statistics on all Agent jobs and their issues so a lot of valuable information on the potential problems that may not necessarily stop the package from execution will be lost. As we will also be using a system table for logging, over time, when you accumulate enough information about the execution process, you can run specialized reports to get the high level overview of the job’s history. Secondly, you will not be able to create a central repository for all messages and metadata about package execution. Also, you will be limited to the arguments which apply to Send Mail task container and a lot of rich functionality e.g. HTML table formatting will not be applicable. I am sure the time and effort sacrificed to set up this neat little solution will pay dividends very quickly so let’s get started.
First thing we want to do it to create a simple table which will store three fields used for notification e-mail content and as a link between system table and package name. This table is necessary as the system table which we will use to get detailed insight into package execution does not carry a field descriptive of the package name, only package ID or GUID. If you want to map the package name (created inside the package via a variable during execution) to the info in the system log table, this solution will provide a bridge between the two. I will explain its functionality in more details soon so for now just follow along.
This post will deal with setting up a simple SSIS package logging to enable the potential issues tracking (warnings, errors, execution statuses etc.) from SQL Server agent job via e-mail message. As with everything in IT/BI, there is more than one way to skin the cat and this procedure may not be applicable to your environment or may require small changes to suit you and your set-up. However, it is really easy to follow and build so at any stage you may want to add something to it to increase functionality, don’t hesitate to jump in, experiment and alter things around.
What I want to achieve through this process is to be able to schedule an SSIS job via SQL Agent service and if the job fails, receive notification via e-mail with detailed information on when, how and why the process failed to execute as expected. Sounds like something you could achieve using Precedence Constraint and Send Mail task, but going that way will only give you limited amount of options to play with. First of all, you will not be able to create detailed reports or get rich statistics on all Agent jobs and their issues so a lot of valuable information on the potential problems that may not necessarily stop the package from execution will be lost. As we will also be using a system table for logging, over time, when you accumulate enough information about the execution process, you can run specialized reports to get the high level overview of the job’s history. Secondly, you will not be able to create a central repository for all messages and metadata about package execution. Also, you will be limited to the arguments which apply to Send Mail task container and a lot of rich functionality e.g. HTML table formatting will not be applicable. I am sure the time and effort sacrificed to set up this neat little solution will pay dividends very quickly so let’s get started.
First thing we want to do it to create a simple table which will store three fields used for notification e-mail content and as a link between system table and package name. This table is necessary as the system table which we will use to get detailed insight into package execution does not carry a field descriptive of the package name, only package ID or GUID. If you want to map the package name (created inside the package via a variable during execution) to the info in the system log table, this solution will provide a bridge between the two. I will explain its functionality in more details soon so for now just follow along.
CREATE TABLE [dbo].[SSIS_Logging_Details] ( [Execution_Instance_GUID] [nvarchar](100) NULL, [Package_Name] [nvarchar](100) NULL, [Execution_Date] [datetime] NULL )
Next, at the start of the package we create an ‘Execute SQL Task’ container which will hold the query to populate the above table with parameters (they get their values from system variables) and package start date and time. This container will become the starting point for package execution. After setting up the connections we need to set up the parameter mapping i.e. assign system variables to the parameters we will use to populate out table. To do this click on the ‘Parameter Mapping’ pane and using two system variables – ‘ExecutionInstanceGUID’ and ‘PackageName’ assign them to the following parameters: @ExecGUID and @PackageName as per below.
Next, still in ‘Execute SQL Task’ container insert the following query into SQL Statement pane to populate our table with the parameters we created before.
INSERT INTO SSIS_Logging_Details (Execution_Instance_GUID, Package_Name, Execution_Date) VALUES (@ExecGUID, @PackageName, GetDate())
This will populate the ‘SSIS_Logging_Details’ table with the GUID, package name and date/time attributes every time the package is run as per below.
Probably the most important part of this process is (and also the easiest one) is configuring the details we want to log during package execution. This is the next step. BIDS makes is remarkably easy and we need to do is to right-click on an empty area of the development pane and choose ‘Logging’ from the options presented. This opens up the logging configuration editor where we can select three key aspects of the logging process: a container to log (points to a part of the package that we want to log, it is located on the left hand side of the editor), setting the log provider type (the log provider is the log event destination location, such as table or file) and log events (which events we wish to capture). Under ‘Containers’ section (left-hand side) we will mark the whole package (top level). Proceeding to ‘Providers and Logs’ section we will select ‘SSIS log provider for SQL Server’ as our provider type. This will allow us to log all events to a SQL table for further querying. We will also need to configure a database we want the system table with the log trail to be created in. This is done under the ‘Configuration’ section of the ‘Providers and Logs’ tab.
Next, we will click on ‘Details’ tab which gives us a list of possible events to choose from. Let’s commit ourselves to the most important ones and choose ‘OnError’, ‘OnTaskFailed’ and ‘OnWarning’ as these are the ones that usually indicate the package failed to execute as expected. When finished, this task will also create a system table (dbo.sysssislog) in the database we select when configuring ‘Providers and Logs’ which stores all the package execution data (relating to the events chosen) and which we will use as a primary source for our e-mail content when sending out a message.
The final thing left for us to do is configure the tasks responsible for alerting the operator/administrator of potential issues with the package when it fails. In this case we will do it via another ‘Execute SQL Task’ container which attaches to the rest of the package through a ‘Precedence Constraint’ and has a value set to ‘Fail’ i.e. Red connection line (SQL Server 2008 R2) as per below.
We can get notified of package failure using many different techniques but as I would like to customize the massage output a little bit via HTML, so msdb.dbo.sp_send_dbmail system stored procedure gives me that functionality. The code below represents what happens when the package fails. First, we create a Temp table (#Temp_Fail) with the attributes we will use as an e-mail content and populate it with the data gathered from two sources – system table (dbo.sysssislog) which was created automatically when Logging Configuration Editor was configured and SSIS_Logging_Details table we created at the start. Next, we join those two tables together to populate the temporary table (#Temp_Fail). The reason why I’m joining those two tables together is because system table does not carry a field indicating package name (it is always more convenient to refer to the package by its name rather then GUID or execution ID) and by linking Execution_Instance_GUID field (minus the curly braces hence using RIGHT, LEFT and LEN functions) from SSIS_Logging_Details and ExecutionID field from dbo.sysssislog I can refer to the package by its name. Lastly, I use system stored procedure to parse the content of the Temp table via an e-mail in a HTML format.
--CREATE TEMP TABLE
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#Temp_Fail]') AND type in (N'U'))
DROP TABLE [dbo].[#Temp_Fail]
GO
CREATE TABLE [dbo].#Temp_Fail
(
[Package_Name] [nvarchar](100) NULL,
[Execution_Instance_GUID] [nvarchar](100) NULL,
[Package_Run_Start_DateTime] [datetime] NULL,
[Source] [nvarchar](2000) NOT NULL,
[Event] [sysname] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[Event_Start_Time] [datetime] NOT NULL,
[Event_End_Time] [datetime] NOT NULL
)
--POPULATE TEMP TABLE WITH THE DATA FROM 'dbo.SSIS_Logging_Details' TABLE AND
--'dbo.sysssislog' SYSTEM TABLE SELECTING MAXIMUM EXECUTION DATE ONLY TO NARROW
--THE DETAILS TO THE LAST PACKAGE RUN. THE JOIN IS PERFORMED ON 'Execution_Instance_GUID'
--FIELD FROM 'dbo.SSIS_Logging_Details' TABLE (STRIPPING OFF THE CURLY BRACES VIA 'LEFT()',
--'RIGHT()' and 'LEN()' FUNCTIONS) AND 'executionid' FIELD FROM dbo.sysssislog SYSTEM TABLE
INSERT INTO #Temp_Fail
(Package_Name,
Execution_Instance_GUID,
Package_Run_Start_DateTime,
[Source],
[Event],
[Message],
[Event_Start_Time],
Event_End_Time)
SELECT
Package_Name,
Execution_Instance_GUID,
CONVERT(NVARCHAR (20),Package_Run_Start_DateTime, 120)as Package_Run_Start_DateTime,
[Source],
[Event],
[Message],
Event_Start_Time,
Event_End_Time
FROM
(SELECT
Package_Name,
Execution_Instance_GUID,
Package_Run_Start_DateTime,
[Source],
[Event],
[Message],
Event_Start_Time,
Event_End_Time
FROM
(SELECT
dbo.SSIS_Logging_Details.Package_Name,
dbo.SSIS_Logging_Details.Execution_Instance_GUID,
dbo.SSIS_Logging_Details.Execution_Date AS Package_Run_Start_DateTime,
dbo.sysssislog.[Source],
dbo.sysssislog.[Event],
dbo.sysssislog.[Message],
dbo.sysssislog.starttime as Event_Start_Time,
dbo.sysssislog.endtime as Event_End_Time
FROM dbo.SSIS_Logging_Details INNER JOIN
dbo.sysssislog ON RIGHT(LEFT(dbo.SSIS_Logging_Details.Execution_Instance_GUID,
LEN(dbo.SSIS_Logging_Details.Execution_Instance_GUID) - 1),
LEN(LEFT(dbo.SSIS_Logging_Details.Execution_Instance_GUID,
LEN(dbo.SSIS_Logging_Details.Execution_Instance_GUID) - 1)) - 1)
= dbo.sysssislog.executionid
WHERE (dbo.SSIS_Logging_Details.Execution_Date =
(SELECT MAX(Execution_Date) AS Expr1
FROM dbo.SSIS_Logging_Details AS SSIS_Logging_Details_1))
GROUP BY dbo.SSIS_Logging_Details.Package_Name,
dbo.SSIS_Logging_Details.Execution_Instance_GUID,
dbo.SSIS_Logging_Details.Execution_Date,
dbo.sysssislog.[Source],
dbo.sysssislog.[Event],
dbo.sysssislog.[Message],
dbo.sysssislog.starttime,
dbo.sysssislog.endtime)
AS Details)
AS Details
--CREATE HTML TABLE STRUCTURE, STORE IT IN A VARIABLE
--AND POPULATE IT WITH THE DATA FROM TEMP TABLE
DECLARE @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>RTID Operational Data Store Refresh Failure Details</H1>' +
N'<table border="1">' +
N'<tr><th>
Package Name </th>' +
N'<th>Exec Instance GUID</th>' +
N'<th>Pckg Run Start</th>' +
N'<th>Source</th>' +
N'<th>Event</th>' +
N'<th>Message</th>' +
N'<th>Event Start Time</th>' +
N'<th>Event End Time</th></tr><font size="2"' +
CAST ( ( SELECT td = Package_Name, '',
td = Execution_Instance_GUID, '',
td = Package_Run_Start_DateTime, '',
td = [Source], '',
td = [Event], '',
td = [Message], '',
td = Event_Start_Time, '',
td = Event_End_Time, ''
FROM #Temp_Fail
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</font></table>' ;
--SEND THE MESSAGE WITH THE '@tableHTML' CONTENT
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Insert your your profile here',
@recipients = 'operator1@organisation.com; operator2@organisation.com',
@body_format = 'HTML',
@subject = 'Your Package Failed !!!',
@body=@tableHTML,
@importance='High'
--DROP TEMP TABLE
DROP TABLE #Temp_Fail
If you followed along you will be able to use the code pretty much as it is. The only three things you will need to replace or customize in the sample above rely on your internal environment i.e. @Profile_name parameter, @Subject parameter and @Receipients parameter (code lines 121, 122 and 124). All three are specyfic to your set-up only and will need to be populated/customised accordingly. As a result, when the code is envoked, a message is send to the delegated receipients with the content formatted as per below.
