To begin with I wish to clarify the method of sending SSIS Logs by means of e mail for higher understanding. As you would possibly know it can save you SSIS logs in a number of methods for administration functions. On this article our focus is on easy methods to ship SSIS logs to the administrator at any time when an occasion like a package deal failure is occurred. Simply assume that we have now a number of SSIS packages and we’re storing the logs in a SQL Server database. Nearly all the packages are operating over evening. Now, what if a package deal or some packages failed? Usually one of the important actions is {that a} notification e mail must be despatched to the system directors to allow them to know that one thing’s fallacious with the package deal execution. It may very well be extra useful to ship them the related details about the failure. Studying the next strains you’ll be able to deal with this necessary a part of the method in your organisation.
Comply with the method under to ship SSIS logs which might be saved in SQL Server by means of e mail.
A. To begin with create a database in SQL Server to host the SSIS Logs and identify it “SSISLogTest”
B. Create an OLEDB connection pointing to the SSISLogTest and identify it “SSISLogConnection”
C. Proper click on in Management circulate and choose Variables
D. In variables tab, add a brand new variable and identify it “ErrorMessage”. We are going to use this variable to gather and retailer the related logs as XML.
E. Set the variable’s information kind as string.
F. Add one other variable and identify it “SendEmailCounter” and set its datatype as “Int16”. This variable shall be used to forestall sending a number of emails.
G. Proper click on in Management Circulate and choose Logging
H. In configuration SSIS Logs window:
1. Tick the package deal in container part
2. Choose “SSIS log supplier for SQL Server” as supplier kind
3. Click on Add… button
4. Tick the log for the package deal
5. Choose SSISLogConnection from the drop down
6. Click on “Particulars” tab, then choose the occasions you wish to log from the listing and click on OK. (On this instance I chosen OnError, OnTaskFailed and OnWarning)
I. Go to “Occasion Handlers”
1. Choose the package deal from Executable listing and click on OK
2. Choose “OnError” from occasion handler listing
3. Click on on “Click on right here to create an ‘OnError’ occasion handler for executable ‘Ship Logs’” (Ship Logs is the package deal identify)
J. Drop an “Execute SQL Process” on occasion handler space and identify it “Learn Logs”
1. Double click on on the Learn Logs process to open Execute Process Editor
2. In Common, SQL Assertion part make it possible for “OLE DB” is chosen as “ConnectionType” and “Direct Enter” is chosen as “SQLSourceType”
3. Choose SSISLogConnection from the listing
4. In “Consequence Set” part choose “XML”
5. Now in Parameter Mapping, choose “System::ExecutionInstanceGUID” from Variable Identify listing
6. Set its route as “Enter”
7. Set the info kind as “GUID”
8. Put “0” in parameter identify. Because the connection kind that we’re utilizing to connect with SSISLogTest database is an OLEDB connection it is very important use “0” (zero) for the parameter identify.
9. Go away parameter measurement as default.
10. Go to “Consequence Set” part and put “0” as “Resultset Identify”
11. Choose “Consumer::ErrorMessage” from the variable identify listing
12. Go to Common, in SQL Assertion part and put the next SQL code in SQLStatement and click on OK:
choose * from [dbo].[sysssislog]
the place (executionid = ?)
FOR XML AUTO
13. In Execute SQL Process Editor window click on OK.
Ok. Drop a For Loop Container on Occasion Handlers. The aim of utilizing the For Loop Container is to manage the variety of emails that the Ship Mail Process will ship to the recipients. If we don’t use the For Loop Container when an occasion like an error is occurring in an information circulate process, the Ship Mail Process will ship a number of emails. I assume that we have now a package deal degree occasion handler and in addition we have now an information circulate process that incorporates an OLEDB supply part. The rationale of sending a number of emails is that when the OLEDB supply part is failed it reviews an error upwards to the info circulate and it rises as much as the occasion handler.
1. Double click on on the For Loop Container and set EvalExpression as “@[User::SendEmailCounter]<1” and AssignExpression as “@[User::SendEmailCounter]=@[User::SendEmailCounter]+1” then click on OK
2. Join “Learn Logs” to the For Loop Container. (Analysis operation: Constraint, Worth: Success)
3. Put a Ship Mail Process within the For Look Container
4. Double click on on Ship Mail Process and go to Mail and choose <New Connection…> in SmtpConnection to create a brand new SMTP Connection supervisor.
L. Put your SMTP server settings in SMTP Connection Supervisor Editor and click on OK
1. Put an e mail handle in “From”. This e mail handle shall be used to ship the outcomes. It’s often a “NOREPLY” e mail handle.
2. Put the recipients’ emails in “To”. The emails must be semicolon separated. The size of “To” may very well be most of 255 characters based mostly on the web requirements. You possibly can put once more most of 255 character lengthy recipients’ emails in “Cc” and “BCc” components.
3. Sort a related topic in “Topic”.
4. Choose Variable for “MessageSourceType”
5. Choose “Consumer::ErrorMessage” for “MessageSource” and click on OK.
M. Now we should always see one thing like the next screenshot in Occasion Handlers tab
N. It’s virtually accomplished now. To check our design we will change one thing to create an error; for example, you’ll be able to change a vacation spot mapping in an OLEDB vacation spot in an information circulate and run the package deal. As you’ll be able to see the package deal fails:
O. Now go to Occasion Handlers tab and also you’ll see that the method is efficiently accomplished:
P. Verify your mailbox and also you’ll see that you simply’ve acquired an e mail. All Performed.
Error Message Evaluation:
As we talked about it is best to obtain an e mail containing the XML of the package deal log in its physique. It may very well be one thing just like the under screenshot:
There is essential info within the XML that may make it easier to to resolve the package deal failure issues in actual world. I’ve highlighted the message with 6 necessary components and you’ll see a quick description under:
1. Supply: Identify of the thing that creates the error or warning (it relies on what you’ve chosen on “Particulars” tab from “Configure SSIS Logs”. We mentioned this in part H quantity 6.) On this case “Ship Logs” is the identify of the package deal.
2. ExecutionID: Everytime you run a package deal a GUID is creating for the actual package deal run. So by utilizing this selection you’ll be able to ensure that the logs which might be despatched to you’re associated to present package deal run. It is vitally helpful particularly when you’re utilizing a single log database for storing all SSIS packges’ logs. So may very well be difficult to determine that the logs are belong to which package deal.
3. Begin Time: Exhibits the precise operating time of the package deal
4. Occasion: Signifies the occasion identify
5. Supply: Once more, it reveals the thing that causes the occasion. Right here it’s a information circulate process named “DF Desk 1”.
6. Message: Exhibits the outline of the occasion.
For extra readability it can save you the e-mail content material as an XML file by copying the e-mail content material and pasting the content material right into a notepad and reserve it as an XML file. Then you’ll be able to open the XML file with Microsoft Excel. The outcome must be one thing like picture under and as you’ll be able to see it’s way more readable than the XML model:
So now you’ll be able to filter the XML file utilizing excel and use it successfully.
Associated
Supply hyperlink
