Cube processes fine in SSAS and fine in SSIS but won't process using SQL Agent (JOB)
So I have a cube built in SSAS 2005. The cube processes fine when processing in SSAS, it processes fine in SSIS. However, when it gives me an error message when I try to schedule it as a step in a job. All of the ways give the same error message
I have tried the following 3 ways to get this cube scheduled to run so I don't have to do it manually.
1. I created an SSIS package to run the cube. This processes fine in the package run I run it locally. But as soon as I schedule the package to run from the SQL job it fails.
2. I created a step in the job to run as Type: SQL Server Analysis Services Query. Run as: SQL Agent Service Account for myserver and mydatabase. Dimensions are processed in one step and the Cube is set to process in the next step. Either way they both fail
individually in the job.
3. I created a step in the job to run as Type: SQL Server Analysis Services Command. Run as: SQL Agent Service Account for myserver. Dimensions are processed in one step and the cube is set to process in the next step. They both fail.
Date 27/04/2011 1:44:56 PM
Log Job History (NAV_OLAP database and cube generation)
Step ID 12
Server SYKSQLREPORT
Job Name NAV_OLAP database and cube generation
Step Name test step dimensioin
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT\Admin. ...ion: A connection cannot be made. Ensure that the server is running. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException:
An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset,
Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.Xmla.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.Xmla.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.Xmla.DimeRecord..ctor(Stream stream)
at Microsoft.An. The step failed.
I have the cube set up as NT Authentication, I have the SSIS package set up with NT authentication. The owner of the job is the NT\Admin user which has sysadmin privilages. I am not sure what other information you will need in order to help me out.
Please help :)
April 27th, 2011 1:50pm
If I am not mistaken, the user that the SQL Agent Service is configure to run as will be the user context that the processing will happen as (not the job owner). Make sure the SQL Agent Service service account has all the necessary permissions to process
the cube.
Craig Bryden - Please mark correct answers
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2011 10:16pm
Hi LS78,
This forum focus on the issue about Reporting Services, so I would recommend you post your issue at this SSAS forum
http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/threads There are many professional engineers, you will receive more replies. Thanks for your understanding.
Regards,
Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.
April 29th, 2011 2:06am
This is all good now.
It looks as though it was a server name issue. I was uing the alias name and not that actual server name.
Thank you for your help
LS
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2011 4:23pm