How to process a cube in a SQL Agent job

It can be done but it’s not well documented and it’s complicated by the fact that you can’t easily get the result of the process. Unless you actively check and manually raise an error if there’s been a problem, the job will report sucess no matter how screwed up the cube processing is. That part is all about the XML results that are output by the process.

Here’s how I do it (and I’d be very interested if you have any suggestions to improve it):

Connect to the Analysis Services server in SQL Server Management Studio.

Right click on the database and select Process.

Configure all the options and then use the Script button to grab the XML that defines the processing job. It will look something like this:

<Batch xmlns="">
    <ErrorConfiguration xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="">
    <Process xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="">

Create a new SQL Agent job.

Add a step to get rid of any old output file(s). Change the step type to CmdExec and paste in this code, modifying the file name:

if exist "\\rep01\joblogs\ProcessASOnBI1Log.xml" (del "\\rep01\joblogs\ProcessASOnBI1Log.xml")

Add a step for the processing. Change the step type to SQL Server Analysis Services Command and paste in the XML. Go to the Advanced page and give it an XML output file – with the same name you used in the previous step – to write its results to.

Add a step to check the output XML file to see whether the process was successful. Change the step type to T-SQL and paste in this code:

-- Check the result files from the cubes
set nocount on;
create table #File
        ResultFileName        varchar(255)

insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI1Log.xml');
insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI2Log.xml');

create table #FileContents 
        LineNumber        int identity
        , LineContents        nvarchar(4000)
declare @FileContents        nvarchar(4000)
        , @NewLine        char(2)
        , @FileName        varchar(255)
        , @CubesFailed        bit
        , @CmdLine        varchar(300)
set @NewLine = char(13) + char(10);
set @CubesFailed = 0;
-- Loop through result files
declare file_cursor cursor
local forward_only 
select        ResultFileName
from        #File;
open file_cursor
        fetch next from file_cursor 
        into         @FileName
        while @@fetch_status = 0
                set @CmdLine = 'type ' + @FileName;
                insert #FileContents
                exec master.dbo.xp_cmdshell @CmdLine;
                select        @FileContents = isnull(@FileContents, '') + @NewLine + isnull(LineContents, '')
                from        #FileContents;
                select @FileName;
                select @FileContents;
                set nocount off;
                if @FileContents like '%error%'
                        set @CubesFailed = 1
                delete #FileContents;
                -- Get the next cursor row
                fetch next from file_cursor 
                into         @FileName
close file_cursor
deallocate file_cursor
drop table #FileContents;
drop table #File;
if @CubesFailed = 1
        raiserror('Cubes failed to build.', 16, 1)

Modify it to add your own file name(s) into #File at the top. This step will raise an error if the processing failed. If you don’t do this, you won’t know whether the job has worked.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s