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.

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.

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.

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.

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

Email: bmcdonald@pragmaticworks.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald