Troubleshooting SSIS errors can be challenging in themselves, so retrieving the correct error messages should not be, especially in time-sensitive production environments.
I am going to demonstrate how to set up and always deliver the correct, well-formatted SSIS errors when a Package fails. This may save you or anyone the trouble of digging around in Event logs and SQL Agent Jobs for the right SSIS error messages. This SSIS setup also makes you non-dependent on anyone for this information in any environment.
SSIS Error logging
SSIS errors can either be tracked using a custom logging setups or the out-of-the box logging system which can easily be configured to capture errors besides other Events. Each of these logging strategies however requires you to extract the necessary error from the logging repository after a package fails. In most cases this might require querying tables or some form of digging around to get the actual error message after it’s been logged.
Using the SSIS Event Handler in addition to your logging option you can have SSIS deliver the right error details to your mailbox whenever or wherever the Package fails. I am going to use the out-of-the-box SSIS logging mechanism to demonstrate how this works. The approach I am going to describe can be used for both type of logging mentioned above, however to employ this exact approach your logging repository must be a Table.
Setting up SSIS Packages to Email Formatted Errors upon failure
Configuring a Package to email an error report anytime it fails involves 4 steps;
- Enabling Error logging on Packages and Tasks
- Creating a Stored Procedure that will retrieve and pass the error from a log table to the Database Mail System.
- Creating a SQL Task that will call the Store Procedure.
- Assigning (copying and pasting) the SQL task to any Package’s Event Handler, so that anytime the Package fails it will send out an email with the specific error(s) that occurred.
Enabling SSIS logging with SQL Server log Provider
I am going to use the out-of-the box SSIS logging mechanism to demonstrate how this process works.
To set up logging, right click an empty space in your Package and click on Logging. Under Container, check the Package Container, on the Providers and log tab select SSIS log provider for SQL Server from the Provider Type drop down. Check the SSIS log provider for SQL Server and under configurations select the Connection Manager that has the database with your logging repository table. For the purposes of serving errors, make sure you enable the OnError event for the Package on the details tab as shown if fig 1 below.
Fig 1. Showing OnError Package Event.
Creating the Error Report Delivery Store procedure
The logic for the Stored Procedure that will retrieve and pass the error from the error log table to the Database Mail System is as shown in Listing 1 below.
CREATE PROCEDURE [dbo].[Htmlssiserroremail] (
@UserEmail NVARCHAR(4000),
@executionid UNIQUEIDENTIFIER,
@PackageName NVARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EmailSubject NVARCHAR(2000),
@executionidStr NVARCHAR(50),
@HTMLContent NVARCHAR(MAX),
@Query NVARCHAR(2000)
SELECT @executionidStr = CONVERT(NVARCHAR(50), @executionid),
@EmailSubject = @PackageName + ' Package Error';
WITH LatestRun
AS (SELECT
Row_number() OVER (Partition BY executionid ORDER BY id ASC )
AS ErrorOrder,
s.*
FROM sysssislog s
WHERE event = 'OnError'
AND executionid = @executionidStr),
ErrorLogged
AS (
SELECT
ErrorOrder,
computer,
Operator,
source,
Starttime,
Endtime,
datacode,
Message
FROM LatestRun
WHERE ErrorOrder < 5
)
SELECT @HTMLContent = N'
<style type="text/css">
.style1
{
width: 100%;
border-left-style: solid;
border-left-width: 1px;
border-right: 1px solid #C0C0C0;
border-top-style: solid;
border-top-width: 1px;
border-bottom: 1px solid #C0C0C0;
background-color: #FFFFCC;
}
.style2
{
font-weight: bold;
}
.style3
{
color: #003399;
font-weight: bold;
}
.style4
{
color: #A0A0A0;
}
</style>'
+ N'<H1 class="style3">SSIS Error Report</H1>'
+ N'<table border="1">'
+ N'<tr><th class="style2">ErrorOrder</th>'
+ N'<th class="style2">Computer</th><th class="style2">Operator</th>'
+ N'<th class="style2">Package Name</th><th class="style2">Source Name</th>'
+ N'<th class="style2">Start Time</th><th class="style2">End Time</th>'
+ N'<th class="style2">Decimal Code</th> <th class="style2">Error Message</th>'
+ Cast (
(
SELECT
td = el.ErrorOrder,'',
td = el.computer,'',
td = el.Operator,'',
td =@PackageName,'',
td = el.source,'',
td = el.Starttime,'',
td = el.Endtime,'',
td = el.datacode,'',
td = el.Message,''
FROM ErrorLogged AS el
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
)
+ N'</table>';
EXEC msdb.dbo.Sp_send_dbmail
@profile_name ='Controls',
@recipients=@UserEmail,
@subject = @EmailSubject,
@body = @HTMLContent,
@body_format = 'HTML';
END
GOThe Parameterized Stored Procedure in Listing 1 above uses the current ExecutionInstanceGUID of a Package to obtain the current error(s) logged to the SSIS log table. The returned error rows are reformatted as HTML documents and then passed in conjunction with recipient(s) email addresses to the Parameterized dbo.sp_send_dbmail System Stored Procedure.
Note, sp_send_dbmail System Stored Procedure which is stored in the MSDN database and can also be configured to send an e-mail message to specified recipient(s) that may include query result set inline or file attachments, or both. I chose the option to pass the resultset as HTML documents because of the flexibility of formatting the results with various styles as shown in Listing 1.
Before you run the Stored Procedure on any Server you must however make sure that the Database Mail system is enabled on the Server. You can check-out Richard Waymire’s article on how to configure Database Mail.
Remember to replace the sysssislog table in the Stored Procedure with the name of the logging table you used to setup your logging if it was different.
Creating SQL task
In this step we are going to configured the SQL Task that will execute the parameterized Stored Procedure above. In the SQL task, two System Variables and one User Variable are passed to the SP as shown in fig 2 below.
Fig 2. Showing SQL Task setup.
The User Variable must be setup at the Package scope with semicolon separated email addresses of all recipients of the error as shown in fig 3 below.
Fig 3. Showing User Email-Address Variable setup.
I normally will configure this value in a configuration file so that it is easy to change.
The two System Variables that are passed as the remaining parameters are the “ExecutionInstanceGUID” and “PackageName” as shown in fig 4 below.
Fig 4. Showing the system variables used in this proccess
Assigning SQL task to Event handler of a Package
When completed, the SQL task configured in the previous section can then be passed (copy and pasted) to the OnError Event handler of any Package to capture and email the appropriate error it to the recipients. To assign the task to the Event handler of any Package, open the Package, go to the Event handler tab on the Package, under the Executables dropdown select the Package and on the Event handler dropdown select OnError from the dropdown list. Now configure the SQL task describe above or copy and paste it in the Event Handler as shown on fig 5 below if you have already configured it elsewhere.
Just make sure any Package that is assigned this task have the UserEmail Variable.
Fig 5. Showing SQL Task assigned to an Event handler
This task will be fired at the Package level by the OnError event whenever an error occurs. The idea being that, ultimately the Package Container generates OnError event if any task on the Package Container or the Package itself errors out.
SSIS Error Email Report
Fig 6 below shows a sample error report sent to my mailbox when a Package I have configured using the steps above failed. Some stuff to note, besides font and color formatting is the ErrorOrder column on the report. Through the store procedure we created above, the report has been designed to shows the first four error messages if available, the ErrorOrder column indicate the logging order in which the errors occurred, with 1 being the first error that occurred in failed Package Execution process.
Fig 6. Showing an error Report Emailed on failure of an SSIS Package.
Many of the error messages that you may encounter may come from other components that are not directly related Integration Services components but to other components such as OLE DB providers and other database components such as the Database Engine and Analysis Services, file system, SMTP server, MSMQ and so forth. Such components generates very generic error messages when they cause a package to fail so It is good practice to retrieve more than the first error that occurred as I have done in this process. This is important because sometimes the subsequent errors may provide some information on why the non SSIS Components failed. Remember to consider subsequent errors only if the initial errors does not provide enough information.
Also on the report, the Package name is explicitly added to every row of the report even If it is a Task that failed, this information may require extra steps to obtain if you are querying the log table directly. You can use the Decimal Codes on the report to directly search for error messages here of you are not certain of the messages provided directly by SSIS.
Summary
This setup ensures that you always receive the correct error(s) and in the order which they occur with the added option of formatting the results.
In the SP, the style section allows you to format the report to your taste. I go to the extent of color formatting the reports with colors that suite my mood. I figure if you are going to recieve error messages, you might as well get it served the way you like it.