Analysis Services Processing Task Fails When Run By SQL Server Agent

2
votes

I have an SSIS package which contains an Analysis Services Processing Task. This package is kicked off by a SQL Server Job in SQL Server 2008 R2.

If I run this job myself or process the cube manually everything is fine.

However if I schedule the job and let the SQL server agent run it then the Analysis Services Processing task fails stating Errors in the OLAP storage engine and that an error occurred while processing the one of the measure groups.

Has anyone else every seen anything like this?

4
ssissql-server-2008-r2ssassql-server-agent
Look at the account that is responsible for processing. Does it have permissions on the resource being used for the dimension? – billinkc
What do you mean by the resource being used? – Leigh
Resource = generic term for wherever the dimension data comes from. I assume it's a database but my SSAS is weak. Can the account connect to the database? Can that account access the catalog? Can it perform the necessary query, execute the source proc, whatever? If SSAS can use something other than a database, like a file, ensure the account has file system permissions. – billinkc

4 Answers

4
votes

The SQL Server Agent service account may not have sufficient permissions. You can validate this by doing any of the following:

  1. Add the service account to the Administrators group on the analysis services server to validate this issue. Let the job run as scheduled.
  2. Create a proxy that runs under your credentials and set the job to execute under the proxy. Let the job run as scheduled.
  3. Change the SQL Server Agent to use your credentials. Let the job run as scheduled.

If the job completes successfully after making any of the above changes, then you have a permission issue that you need to resolve.

2
votes

So after months of looking at this I finally realised the answer; of course it's a simple one.

The SSIS job I had created was processing the Cube only, while every time I processed manually in management studio I was processing the whole SSAS database.

I've now changed the SSIS package to process the whole database and everything seems to be working correctly.

0
votes

when This Error Generate : reporting services catalog database file existence failed sql server 2008 r2

================================================================================

This issue occurs because the databases for the SQL Server 2008 Reporting Services instance that you want to install already exist on the computer with following path.

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Manually Remove .MDF and .LDF files of Report from above path and Re-run Setup.

0
votes

In my case I found the solution in other way. First of all I login into analysus services using SQL management studio.Second of all I searched into databases folder the name of my project, after I open that and double clic in Roles folder, clic in new role, in this window I selected Membership in the left pane, clic in add button and search the NT AUTHORITY\Service, clic in Ok button and finally restart Sql analysis services. I tried to run my job and It works. enter image description here