Tips on how to ship extra exact SSIS logs (errors) by means of e-mail


To begin with it’s worthwhile to learn my earlier article known as “Tips on how to ship SSIS logs (errors) by means of e-mail” because the processes are fairly the identical. Nonetheless, it’s worthwhile to create some adjustments within the Execute SQL Job (ref.: part “J” quantity 5 and 12 of “Tips on how to ship SSIS logs (errors) by means of e-mail”). As an Execute SQL Job is used to gather the logs saved in SQL Server it’s worthwhile to be aware of parameter mapping in Execute SQL activity and know the way it works. Assume that we have to simply ship the “Error” logs which are occurred between “Occasion Handler Begin Time” and the present time for the present package deal execution. As you’ll be able to think about it’s barely totally different from what we did within the earlier article to e-mail the SSIS logs to the system directors. As there was only one system variable that we mapped in parameter mapping part within the Execute SQL Job. However, right here we have to have yet another system variable mapped to a parameter. Please be aware that we’re utilizing OLEDB connection to connect with the SSIS log database that we created earlier than to retailer SSIS logs. So there are some essential factors with OLEDB Connection and Execute SQL Job parameter mapping and its SQL assertion.

The way it works?

Contemplating the next notes we’re additionally answering the query “Tips on how to go a system variable to a SQL Assertion in an Execute SQL Job”.

·   Parameter marker for an OLEDB connection is a query mark “?”. It doesn’t matter if we’re querying the primary mapped parameter or the second, so we have to simply use a “?” within the SQL assertion at any time when we wish to level to a parameter within the SQL assertion and the Execute SQL Job will determine the parameters by their sequence within the parameter mapping checklist.

·   Parameter identify for an OLEDB connection needs to be numeric begins from “0” (zero).

·   The sequence of utilizing parameters in SQL Assertion is essential.

·   Click on right here to get extra details about parameters and return codes within the Execute SQL Job

Now, double click on on the Execute SQL Job to open “Execute SQL Job Editor” for “Learn Logs” (ref.: part “J” from “Tips on how to ship SSIS logs (errors) by means of e-mail”) and go to Parameter Mapping.

1.       Choose “System::EventHandlerStartTime” from the variable identify drop down checklist

2.      Route-> Enter

3.      Knowledge Sort-> DATE

4.      Parameter Title-> 1 (we’ve added “System::ExecutionInstanceGUID” earlier than in the earlier article). Once more be aware that the sequence of parameter names in parameter mappings is essential right here.

1

5.      Go to “Basic” part and put the next SQL code in SQLStatement half:

2

choose *

from [dbo].[sysssislog]

the place (executionid = ?) and (starttime between ? and getdate()) and [event] = ‘OnError’

FOR XML AUTO

 

Within the above code the primary query mark “?” is pointing to the parameter with the parameter identify of “0” in parameter mapping checklist and the second “?” is pointing to the parameter within the checklist that its parameter identify is “1”. The logs are restricted to error logs solely by assigning ‘OnError’ to “Occasion” column.

6.      Click on OK twice. Now run the package deal and also you’ll obtain an e-mail containing all errors occurred between occasion handler begin time and present time for the present package deal execution.

Why the sequence of utilizing parameters in SQL Assertion is essential?

 

Let’s take a look at what is occurring if the sequence of utilizing the mapped parameters in SQL assertion just isn’t matched to the sequence of parameter names.

1.       Open Execute Job SQL Editor once more and alter the parameter identify as beneath and click on OK:

 

3

 

2.      Don’t change the SQL assertion

3.      Run the package deal

 

4

 

4.      Plainly the occasion handler emailed one thing to you, so, it is best to obtain an e-mail.

5.      Check out the e-mail content material. It needs to be one thing like this (be aware to the highlighted half):

<ROOT><?MSSQLError HResult=”0x80040e07″ Supply=”Microsoft SQL Server Native Consumer 11.0″ Description=”Operand kind conflict: datetime2 is incompatible with uniqueidentifier“?></ROOT>

6.      Operand kind conflict: datetime2 is incompatible with uniqueidentifier, means that you’ve got a knowledge kind mismatching within the SQL assertion that maps a date area to a GUID area.

7.      Now, open the “Execute SQL Job Editor” once more and go to Basic-> SQLStatement and modify the SQL assertion as beneath:

choose *

from [dbo].[sysssislog]

the place (starttime between ? and getdate()) and (executionid = ?) and [event] = ‘OnError’

for xml auto

 

8.     Now run the package deal once more.

9.      That’s it. You’re receiving the related error messages by e-mail.



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *