To begin with I wish to clarify the method of sending SSIS Logs via electronic 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 ship SSIS logs to the administrator at any time when an occasion like a bundle 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 the entire packages are operating over evening. Now, what if a bundle or some packages failed? Typically one of the important actions is {that a} notification electronic mail needs to be despatched to the system directors to allow them to know that one thing’s improper with the bundle execution. It might be extra useful to ship them the related details about the failure. Studying the next strains you may deal with this essential a part of the method in your organisation.

Comply with the method beneath to ship SSIS logs which can be saved in SQL Server via electronic mail.

A.     To begin with create a database in SQL Server to host the SSIS Logs and title it “SSISLogTest”

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

C.     Proper click on in Management circulation and choose Variables

D.    In variables tab, add a brand new variable and title it “ErrorMessage”. We’ll use this variable to gather and retailer the related logs as XML.

E.     Set the variable’s information sort as string.

F.     Add one other variable and title it “SendEmailCounter” and set its datatype as “Int16”. This variable can be used to stop sending a number of emails.

G.    Proper click on in Management Move and choose Logging

H.    In configuration SSIS Logs window:

1.       Tick the bundle in container part

2.      Choose “SSIS log supplier for SQL Server”  as supplier sort

3.      Click on Add… button

4.      Tick the log for the bundle

5.      Choose SSISLogConnection from the drop down

6.      Click on “Particulars” tab, then choose the occasions you wish to log from the checklist and click on OK. (On this instance I chosen OnError, OnTaskFailed and OnWarning)

I.       Go to “Occasion Handlers”

1.       Choose the bundle from Executable checklist and click on OK

2.      Choose “OnError” from occasion handler checklist

3.      Click on on “Click on right here to create an ‘OnError’ occasion handler for executable ‘Ship Logs’” (Ship Logs is the bundle title)

J.      Drop an “Execute SQL Job” on occasion handler space and title it “Learn Logs”

1.       Double click on on the Learn Logs process to open Execute Job Editor

2.      In Normal, SQL Assertion part guarantee that “OLE DB” is chosen as “ConnectionType” and “Direct Enter” is chosen as “SQLSourceType”

3.      Choose SSISLogConnection from the checklist

4.      In “Outcome Set” part choose “XML”

5.      Now in Parameter Mapping, choose “System::ExecutionInstanceGUID” from Variable Title checklist

6.      Set its path as “Enter”

7.      Set the information sort as “GUID”

8.     Put “0” in parameter title. Because the connection sort that we’re utilizing to hook up with SSISLogTest database is an OLEDB connection it is very important use “0” (zero) for the parameter title.

9.      Depart parameter measurement as default.

10.  Go to “Outcome Set” part and put “0” as “Resultset Title”

11.   Choose “Consumer::ErrorMessage” from the variable title checklist

12.  Go to Normal, 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 Job Editor window click on OK.

Okay.     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 Job will ship to the recipients. If we don’t use the For Loop Container when an occasion like an error is going on in an information circulation process, the Ship Mail Job will ship a number of emails. I assume that we have now a bundle degree occasion handler and in addition we have now an information circulation process that comprises an OLEDB supply part. The explanation of sending a number of emails is that when the OLEDB supply part is failed it studies an error upwards to the information circulation 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 Job within the For Look Container

4.      Double click on on Ship Mail Job 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 electronic mail tackle in “From”. This electronic mail tackle can be used to ship the outcomes. It’s normally a “NOREPLY” electronic mail tackle.

2.      Put the recipients’ emails in “To”. The emails needs to be semicolon separated. The size of “To” might be most of 255 characters primarily based on the web requirements. You possibly can put once more most of 255 character lengthy recipients’ emails in “Cc” and “BCc” components.

3.      Kind 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 nearly accomplished now. To check our design we will change one thing to create an error; for example, you may change a vacation spot mapping in an OLEDB vacation spot in an information circulation and run the bundle. As you may see the bundle fails:

O.    Now go to Occasion Handlers tab and also you’ll see that the method is efficiently performed:

P.     Test your mailbox and also you’ll see that you just’ve acquired an electronic mail. All Finished.

Error Message Evaluation:

As we talked about you must obtain an electronic mail containing the XML of the bundle log in its physique. It might be one thing just like the beneath screenshot:

There is essential info within the XML that may show you how to to resolve the bundle failure issues in actual world. I’ve highlighted the message with 6 essential components and you’ll see a quick description beneath:

1.       Supply: Title of the article 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 title of the bundle.

2.      ExecutionID: Everytime you run a bundle a GUID is creating for the actual bundle run. So by utilizing this feature you may ensure that the logs which can be despatched to you might be associated to present bundle run. It is vitally helpful particularly when you’re utilizing a single log database for storing all SSIS packges’ logs. So might be sophisticated to determine that the logs are belong to which bundle.

3.      Begin Time: Exhibits the precise operating time of the bundle

4.      Occasion: Signifies the occasion title

5.      Supply: Once more, it reveals the article that causes the occasion. Right here it’s a information circulation 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 put it aside as an XML file. Then you may open the XML file with Microsoft Excel. The end result needs to be one thing like picture beneath and as you may see it’s far more readable than the XML model:

So now you may filter the XML file utilizing excel and use it successfully.