May 17, 2018 at 8:27 am
Hello experts,
When I get an error with an SSIS package, I follow the manual process of right-clicking on the project in SSMS, select Reports, Standard Reports, All Executions.
Is there any way to extract the info that shows up in the execution messages and include it in an email?
Thanks for any help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 21, 2018 at 9:07 am
We did something similar using event handlers to push the error messages to a table.
The start time of the package was logged in the same table (as was the end time).
We then have a process that looks at the table, looks for any error messages after the logged start of the package and sends those out in an email.
May 22, 2018 at 11:16 am
You already marked this as answered but look into using the OnError Event Handlers when creating your packages. It's the only place that has the system variable with the full error message in it.
I use this as part of a full audit log table then a separate step to send out notifications if there's any errors.
May 22, 2018 at 11:21 am
JustMarie - Tuesday, May 22, 2018 11:16 AMYou already marked this as answered but look into using the OnError Event Handlers when creating your packages. It's the only place that has the system variable with the full error message in it.I use this as part of a full audit log table then a separate step to send out notifications if there's any errors.
Out of interest, does this capture all of the error messages for you (in the All Executions reports, there are often several errors), or just one per failed package?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 11, 2018 at 9:01 am
Phil Parkin - Tuesday, May 22, 2018 11:21 AMJustMarie - Tuesday, May 22, 2018 11:16 AMYou already marked this as answered but look into using the OnError Event Handlers when creating your packages. It's the only place that has the system variable with the full error message in it.I use this as part of a full audit log table then a separate step to send out notifications if there's any errors.
Out of interest, does this capture all of the error messages for you (in the All Executions reports, there are often several errors), or just one per failed package?
If you have the package set to fail on error then you get the error that made it faceplant. If you have it continue it will log the error messages that come through for each event handler.
I've found that the message provided is the useful one out of the slew that can occur. Most of the stuff I've deployed with this has been package level so the catalog reports don't get created.
June 12, 2018 at 2:17 am
Phil Parkin - Tuesday, May 22, 2018 11:21 AMJustMarie - Tuesday, May 22, 2018 11:16 AMYou already marked this as answered but look into using the OnError Event Handlers when creating your packages. It's the only place that has the system variable with the full error message in it.I use this as part of a full audit log table then a separate step to send out notifications if there's any errors.
Out of interest, does this capture all of the error messages for you (in the All Executions reports, there are often several errors), or just one per failed package?
This is the sort of information we collect (although there is a lot more you can grab but we didn't find much to be of any use for us):-
[EventID] - An autonumber so we have something to sort by (and not just the time)
[EventDate] - Datetime of the message
[EventType] - Contains Error, Start or End
[Package] - Name of the package that's running
[Process] - The task that's sent the message
[EventText01] - Usually blank unless there's an error - contains the error message if it's failed
[EventText02] - Not currently used but may be useful if we change our processes
[EventInteger] - This is number for the error message (we think!)
Hope this helps somebody......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply