About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture


Report Portal

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:

    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:

    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:

    […] 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 […]