How to: Create an Analysis Services Job Step (SQL Server Management Studio)
This topic describes how to create and define SQL Server Agent job steps that execute SQL Server Analysis Services commands and queries. To run a job step that uses the Analysis Services subsystem, a user must be a member of the sysadmin fixed server role or have access to a valid proxy account defined to use this subsystem. In addition, the SQL Server Agent service account or the proxy must be an Analysis Services administrator and a valid Windows domain account. For more information about defining a proxy account, see How to: Create a Proxy (SQL Server Management Studio)
To create an Analysis Services command job step
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.
For more information on creating a job, see Creating Jobs.
In the Job Properties dialog box, click the Steps page, and then click New.
In the New Job Step dialog box, type a job Step name.
In the Type list, click SQL Server Analysis Services Command.
In the Run as list, select a proxy that has been defined to use the Analysis Services Command subsystem. A user who is a member of the sysadmin fixed server role can also select SQL Agent Service Account to run this job step.
Select the Server where the job step will run, or type the server name.
In the Command box, type the statement to execute, or click Open to select a statement.
The statement must be an XML for Analysis Services Execute method. The statement may not contain a complete SOAP envelope or an XML for Analysis Discover method. While SQL Server Management Studio supports complete Simple Object Access Protocol (SOAP) envelopes and the Discover method, SQL Server Agent job steps do not. For more information about XML for Analysis Services, see XML for Analysis Overview (XMLA).
Click the Advanced page to define options for this job step, such as what action SQL Server Agent should take if the job step succeeds or fails, how many times the job step should be attempted, and where the job step output should be written. Only members of the sysadmin fixed server role can write job step output to a file.
To create an Analysis Services query job step
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.
For more information on creating a job, see Creating Jobs.
In the Job Properties dialog, click the Steps page, and then click New.
In the New Job Step dialog, type a job Step name.
In the Type list, click SQL Server Analysis Services Query.
In the Run as list, select a proxy that has been defined to use the Analysis Services Query subsystem. A user who is a member of the sysadmin fixed server role can also select SQL Agent Service Account to run this job step.
Select the Server and the Database where the job step will run, or type the server or database name.
In the Command box, type the statement to execute, or click Open to select a statement.
The statement must be a multidimensional expressions (MDX) query. For more information about MDX, see MDX Query Fundamentals (MDX).
Click the Advanced page to define options for this job step, such as what action SQL Server Agent should take if the job step succeeds or fails, how many times the job step should be attempted, and where the job step output should be written. Only members of the sysadmin fixed server role can write job step output to a file.
Security
Only members of the sysadmin fixed server role can write job step output to a file. If the job step is run by users who are members of the SQLAgentUserRole database role in the msdb database, then the output can be written only to a table. SQL Server Agent writes job step output to the sysjobstepslog table in the msdb database.