Vidas Matelis Analysis Services Blog

• Home
• About me

Script to automate SSAS partition management (SQL + SSIS)

July 5th, 2009 by Vidas Matelis

Few years ago on my blog I posted scripts “SSIS Package to drop/create partitions based on partition list in the SQL Server table” and “SSIS package that process all partitions/measure groups/cubes in one database“. These posts contained partial scripts that I developed for company “Insight Decision Solutions Inc.” to maintain partitions in the Microsoft SQL Server Analysis Services. This company sells, customizes and implements pre-packaged data warehouse solution (using SQL Server, SSAS, SSIS, SSRS, SharePoint and Excel 2007) for “Life” and “Health” insurance companies. Recently “Insight Decision Solutions Inc.” owners let me post full script on how to automate SSAS partition management . Here are step by step instructions that I adjusted and tested on Adventure Works database. Most of the code comes from my earlier published posts, here I just added information how everything works together.

Read more and download code here…

Posted in SSAS, SSAS 2008 - Katmai, SSIS | 3 Comments »

3 Responses

1. RS Says:
   September 30th, 2009 at 7:17 am

   When I execute the code I get the following error:
   Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

2. pery Says:
   October 20th, 2009 at 12:34 am

   How to automate the SQL’s using SSIS and schedule them for nightly run to send the result (if there are outputs) to the notification email id

3. Using Integration Services with tabular models - MSDN Blogs Says:
   September 8th, 2011 at 3:59 pm

   […] You are better off reading some of the great examples that already exist.  Vidas Matelis has a great example SSAS partition management script you can look at. You can also review the Project Real white paper by Dave Wickert – at the end […]