How to send SSIS logs (errors) through email

First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.

Follow the process below to send SSIS logs that are stored in SQL Server through email.

A.     First of all create a database in SQL Server to host the SSIS Logs and name it “SSISLogTest”

B.     Create an OLEDB connection pointing to the SSISLogTest and name it “SSISLogConnection”

C.     Right click in Control flow and select Variables

D.    In variables tab, add a new variable and name it “ErrorMessage”. We will use this variable to collect and store the relevant logs as XML.

E.     Set the variable’s data type as string.

F.     Add another variable and name it “SendEmailCounter” and set its datatype as “Int16”. This variable will be used to prevent sending multiple emails.

G.    Right click in Control Flow and select Logging

H.    In configuration SSIS Logs window:

1.       Tick the package in container section

2.      Select “SSIS log provider for SQL Server”  as provider type

3.      Click Add… button

4.      Tick the log for the package

5.      Select SSISLogConnection from the drop down

6.      Click “Details” tab, then select the events you want to log from the list and click OK. (In this example I selected OnError, OnTaskFailed and OnWarning)

I.       Go to “Event Handlers”

1.       Select the package from Executable list and click OK

2.      Select “OnError” from event handler list

3.      Click on “Click here to create an ‘OnError’ event handler for executable ‘Send Logs’” (Send Logs is the package name)

J.      Drop an “Execute SQL Task” on event handler area and name it “Read Logs”

1.       Double click on the Read Logs task to open Execute Task Editor

2.      In General, SQL Statement section make sure that “OLE DB” is selected as “ConnectionType” and “Direct Input” is selected as “SQLSourceType”

3.      Select SSISLogConnection from the list

4.      In “Result Set” section select “XML”

5.      Now in Parameter Mapping, select “System::ExecutionInstanceGUID” from Variable Name list

6.      Set its direction as “Input”

7.      Set the data type as “GUID”

8.     Put “0” in parameter name. As the connection type that we are using to connect to SSISLogTest database is an OLEDB connection it is important to use “0” (zero) for the parameter name.

9.      Leave parameter size as default.

10.  Go to “Result Set” section and put “0” as “Resultset Name”

11.   Select “User::ErrorMessage” from the variable name list

12.  Go to General, in SQL Statement section and put the following SQL code in SQLStatement and click OK:

select * from [dbo].[sysssislog]

where (executionid = ?)

FOR XML AUTO

 

13.  In Execute SQL Task Editor window click OK.

K.     Drop a For Loop Container on Event Handlers. The purpose of using the For Loop Container is to control the number of emails that the Send Mail Task will send to the recipients. If we don’t use the For Loop Container when an event like an error is happening in a data flow task, the Send Mail Task will send multiple emails. I assume that we have a package level event handler and also we have a data flow task that contains an OLEDB source component. The reason of sending multiple emails is that when the OLEDB source component is failed it reports an error upwards to the data flow and it rises up to the event handler.

1.       Double click on the For Loop Container and set EvalExpression as “@[User::SendEmailCounter]<1” and AssignExpression as “@[User::SendEmailCounter]=@[User::SendEmailCounter]+1” then click OK

2.     Connect “Read Logs” to the For Loop Container. (Evaluation operation: Constraint, Value: Success)

3.      Put a Send Mail Task in the For Look Container

4.      Double click on Send Mail Task and go to Mail and select <New Connection…> in SmtpConnection to create a new SMTP Connection manager.

L.     Put your SMTP server settings in SMTP Connection Manager Editor and click OK

1.       Put an email address in “From”. This email address will be used to send the results. It is usually a “NOREPLY” email address.

2.      Put the recipients’ emails in “To”. The emails should be semicolon separated. The length of “To” could be maximum of 255 characters based on the internet standards. You can put again maximum of 255 character long recipients’ emails in “Cc” and “BCc” parts.

3.      Type a relevant subject in “Subject”.

4.      Select Variable for “MessageSourceType”

5.      Select “User::ErrorMessage” for “MessageSource” and click OK.

M.   Now we should see something like the following screenshot in Event Handlers tab

N.    It is almost completed now. To test our design we can change something to create an error; for instance, you can change a destination mapping in an OLEDB destination in a data flow and run the package. As you can see the package fails:

O.    Now go to Event Handlers tab and you’ll see that the process is successfully done:

P.     Check your mailbox and you’ll see that you’ve received an email. All Done.

Error Message Analysis:

As we mentioned you should receive an email containing the XML of the package log in its body. It could be something like the below screenshot:

There is very important information in the XML that will help you to solve the package failure problems in real world. I’ve highlighted the message with 6 important parts and you can see a brief description below:

1.       Source: Name of the object that creates the error or warning (it depends on what you’ve selected on “Details” tab from “Configure SSIS Logs”. We discussed this in section H number 6.) In this case “Send Logs” is the name of the package.

2.      ExecutionID: Whenever you run a package a GUID is creating for the particular package run. So by using this option you can be sure that the logs that are sent to you are related to current package run. It is very useful especially when you are using a single log database for storing all SSIS packges’ logs. So could be complicated to identify that the logs are belong to which package.

3.      Start Time: Shows the exact running time of the package

4.      Event: Indicates the event name

5.      Source: Again, it shows the object that causes the event. Here it is a data flow task named “DF Table 1”.

6.      Message: Shows the description of the event. 

For more readability you can save the email content as an XML file by copying the email content and pasting the content into a notepad and save it as an XML file. Then you can open the XML file with Microsoft Excel. The result should be something like image below and as you can see it is much more readable than the XML version:

So now you can filter the XML file using excel and use it effectively.