2

I have a SASS cube on one server and a SSIS package on another server that triggers processing the cube. The SSIS package is executed via a scheduled SQL Agent job. I have enabled error reporting for the SSIS package via an OnError event handler that sends an email message with the MessageSource property set to @[System::ErrorDescription], but this has two problems:

The first problem is that I receive 4 emails for every failure, containing:

  • "Internal error: The operation terminated unsuccessfully."
  • "Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation."
  • "Errors in the OLAP storage engine: An error occurred while processing the 'XXX Fact' partition of the 'XXX Fact' measure group for the 'XXX' cube from the XXX database."
  • "Server: The current operation was cancelled because another operation in the transaction failed."

Only the third of these is useful to me, so is it possible to prevent the others from being sent?

The second problem is that even the third error message is only barely useful - if I process the affected cube manually on the SSAS server, I get the real error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_XXXFact', Column: 'XXXId', Value: 'YYY'. The attribute is 'XXX Id'.

and:

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute XXX Id of Dimension: Dim XXX from Database: XXX, Cube: XXX, Measure Group: XXX Fact, Partition: XXX Fact, Record: YYY.

How can I have the OnError handler in my SSIS package retrieve these detailed error messages and send them via email instead of the vague "An error occurred" messages it currently sends?

Improve this question
2
  • 2
    Not having much experience with SSAS, but would you try setting the job steps' logging to file (advanced -> output file + include step output)? This might show more detailed errors.
    – Marian
    Commented Jul 25, 2016 at 11:24
  • Thanks for the suggestion, @Marian - I think I'll end up logging into a table and generating reports and alerts from there.
    – Kendall Lister
    Commented Aug 10, 2016 at 0:21

1 Answer 1

Reset to default
2

It's been a while since I did this and I don't have a test setup, but to get a single event you should probably use the OnTaskFailed event instead of the OnError.

That won't get you the error you see when processing the cube manually but should at least give you a single mail.

See this article on SSIS error handling.

OnTaskFailed

This event is generated only once when a Task "failure", independently of failure occurrence be in the Package context or just in a Task specific.

Improve this answer
3
  • Thanks for the tip, @tom-v - I'll give it a go and see what I get.
    – Kendall Lister
    Commented Aug 10, 2016 at 0:23
  • @KendallLister how did it work? I'm honestly interested :)
    – Tom V
    Commented Aug 31, 2016 at 19:54
  • Yes, using OnTaskFailed caused only a single email to be sent, which is an improvement, but as you said the actual error is still not available. In fact, even less information is available as the ErrorDescription variable is not available in the OnTaskFailed context.
    – Kendall Lister
    Commented Sep 24, 2016 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.