Blog Post

Automating the Processing of a Measure Groups Index Using SSIS


In a recent post (Processing a Measure Groups Index Using SSMS), I showed you how to utilize SQL Server Management Studio to process just the index of a measure group. In this post, I am going to show you how you can script the processing index out and then run it from within an SSIS package. If you followed along in my last post, I will pick up on step number 4 where I updated the Processing Option to “Process Index”.


Step 4 - Continued: Change Processing Options to “Process Index”

Update the Process Options by selecting the down arrow and then choosing “Process Index” as depicted in my screenshot below with a red square and yellow highlight.

Change Processing Option - Brian K McDonald 

Now script it out by clicking on the down arrow next to the script button and select “Script Action to New Query Window” like shown below.

Script it out - Brian K McDonald 


After the script is generated, click Cancel on the Process Measure Group window to see the results of scripting out to a new query window. Results may look something like the XMLA script shown below.

Resulting XMLA - Brian K McDonald 

You could just hit F5 or the “Execute” button right here within SSMS, but that’s not what I want to show you. Now we get to the fun part! I am going to use this script here in an SSIS package. I am going to fire up BIDS (Business Intelligence Development Studio) and create a new package. Then I’m going to drag an Analysis Services Execute DDL Task onto the Control Flow and then double click it to open the editor. Since I have my cube on my local box, I set up a connection to my localhost. The SourceType property is DirectInput by default, but I could choose to load my XMLA script via a file connection or a variable. I chose to just paste my XMLA script above into the SourceDirect property (shown below) and clicked OK to save my settings.

Execute DDL Editor - Brian K McDonald 


If all goes well, I can execute my package and it will return green. J In this case, it did…So now, I could deploy this package and schedule it to be executed as part of a maintenance plan for the cube.


I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.



Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works


Blogs: SQLBIGeek | SQLServerCentral | BIDN | SQLServerPedia

Twitter: @briankmcdonald





You rated this post out of 5. Change rating




You rated this post out of 5. Change rating