Sql-server – How to get the actual SSAS error when processing a cube from a SQL Agent job

sql serversql-server-agentssasssis

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?

Best Answer

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.

Related Question