Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Create Analysis Services partitions from a SSIS package

I have recently been working on a project implementation that incorporated SQL Server Analysis Services (SSAS) and there was a requirement to be able to create partitions on-the-fly from our ETL process running on SQL Server Integration Services (SSIS). I looked into using XML for Analysis (XMLA) to do this but after some trial and error decided to go with the Analysis Management Objects (AMO) object model instead which can be called from a SSIS Script Task. I built a parameterizable SSIS package that could be called from anywhere in our ETL process and thought it would be useful to share - hence this blog entry.

Some points you should note about this code before you try and use it:

  • It assumes the existence of a partition that has the same ID as that of the containing measure group. This is usually the default partition.
  • It uses the Partition.Clone() method to create a copy of that partition hence you will need to change the 'Source' property of the new partition so that it is populated with the appropriate data. This is entirely dependent on your business logic so if you look in the attached package you will see the following line of code "queryString = "---" 'Set query string here as appropriate". That needs changing.
  • The code doesn't change the 'Slice' property of the created partition so you may want to add code to affect that too if required.
  • There's quite a lot of code here but most of it is just boiler plate stuff. The bit you're really interested in is the CreatePartition(ByRef mg As MeasureGroup, ByVal partitionID As String) method.
  • The code takes a database name, cube name, measure group ID & partitionID as parameters. You can change the logic to suit your scenario.
  • The code raises SSIS pertinent events containing information about changes that are being made and any errors that occur (if any).
  • The package was built and tested on SSIS 2005.

A demo package has been uploaded to my Skydrive and here is the link:

Comments are welcome!




If you don't want to download the package then simply copy-and-paste the code from below:

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.AnalysisServices Public Class ScriptMain Const subComponentName As String = "Partition Creator" Public Sub Main() Dim server As New Server Dim databaseName As String Dim cubeName As String Dim measureGroupID As String Dim partitionID As String Dim mg As MeasureGroup Dim cube As Cube Dim vars As Variables Try 'Lock variables Dts.VariableDispenser.LockForRead("User::OLAPDatabaseName") Dts.VariableDispenser.LockForRead("User::OLAPMeasureGroupID") Dts.VariableDispenser.LockForRead("User::OLAPPartitionID") Dts.VariableDispenser.LockForRead("User::OLAPCubeName") Dts.VariableDispenser.GetVariables(vars) databaseName = vars("User::OLAPDatabaseName").Value.ToString() cubeName = vars("User::OLAPCubeName").Value.ToString() measureGroupID = vars("User::OLAPMeasureGroupID").Value.ToString() partitionID = vars("User::OLAPPartitionID").Value.ToString() server.Connect(Dts.Connections("olap").ConnectionString) cube = server.Databases(databaseName).Cubes(cubeName) mg = cube.MeasureGroups(measureGroupID) Dts.Events.FireInformation(1, subComponentName, "Server=" + server.Name + " Database=" + databaseName + " Cube=" + cubeName + " MeasureGroup=" + measureGroupID, "", 0, True) 'The real work goes on here. It checks to see if the given measure group has a partition ' named for the given week. If it doesn't then it creates it. If (Not PartitionExists(mg, partitionID)) Then If CreatePartition(mg, partitionID) Then Dts.Events.FireInformation(1, subComponentName, "Creating partition '" + partitionID + "' on measure group '" + mg.ID + "'", "", 0, True) 'Commit changes to the cube cube.Update(UpdateOptions.ExpandFull) Else Dts.Events.FireInformation(1, subComponentName, "Creation of partition '" + partitionID + "' on measure group '" + mg.ID + "' failed!", "", 0, True) End If Else Dts.Events.FireInformation(1, subComponentName, "Partition '" + partitionID + "' on measure group '" + mg.ID + "' already exists!", "", 0, True) End If Catch ex As Exception Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0) Finally vars.Unlock() End Try Dts.TaskResult = Dts.Results.Success End Sub Function CreatePartition(ByRef mg As MeasureGroup, ByVal partitionID As String) As Boolean Dim partition As Partition Dim queryString As String Try 'Clone the default partition and then change its name and ID ' Assumption made here that the measure group has a default partition with the same name partition = mg.Partitions(mg.ID).Clone() partition.ID = partitionID partition.Name = partition.ID queryString = "---" 'Set query string here as appropriate CType(partition.Source, QueryBinding).QueryDefinition = queryString Dts.Events.FireInformation(1, "", "Changing query string of Partition '" + partitionID + "' to '" + queryString + "'", "", 0, True) 'Add the new partition to the measure group mg.Partitions.Add(partition) 'Commit the changes mg.Update(UpdateOptions.ExpandFull) Return True Catch ex As Exception Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0) Return False End Try End Function Function PartitionExists(ByVal mg As MeasureGroup, ByVal partitionID As String) As Boolean Dim returnVal As Boolean Dim partition As Partition returnVal = False 'Loop over partitions. If it finds the one its after, flip a boolean flag For Each partition In mg.Partitions If partition.ID = partitionID Then returnVal = True End If Next Return returnVal End Function End Class


N.B. Code formatting was done using Steve Dunn's code formatter plugin for Windows Live Writer.

Published Monday, September 15, 2008 2:06 PM by jamie.thomson



SSAS-Info.com said:


Link to this post was added to our website in the [Analysis Services]/[AMO] section:


September 15, 2008 11:49 PM

Dew Drop - September 16, 2008 | Alvin Ashcraft's Morning Dew said:

September 16, 2008 3:06 PM

Cliff said:

Your timing is impecible. We've had a few cube conversations about how to do this over the last few weeks off and on and this will definately save us time. Thanks!

September 18, 2008 8:51 PM

Ells said:


I am having a problem doing a very similar task to you. I have added the code below to show how far I have got.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

   Public Sub Main()

       Dim oServer As String

       Dim oDatabase As String

       Dim server As New Server

       Dim mg As New MeasureGroup

The error I seam to have highlights the word Server and the exclamation mark when you hover over it says that the type 'Server' is not defined. The same happens to Measure gorup.

I noticed when tyoing in the Import statement for AnalysisServices the dropdown list for Microsoft did not include Analysis Services.

Any suggestions would be gratefully receieved.



October 29, 2008 11:47 AM

jamie.thomson said:

Hi Ells,

You should add a reference to an assembly called Microsoft.AnalysisServices


October 29, 2008 1:02 PM

Ells said:

Thanks. That sounds right. Now any chance of dumbing down the response so I can understand as I am very new to this scripting.

Sorry to be such a pain but I never realised anything more than adding the import statement was required.


October 29, 2008 1:43 PM

jamie.thomson said:


Within the solution explorer (or perhaps its called project explorer - can't remember) Right-click on "refernces"  and select "Add reference". You should be able to browse for Microsoft.AnalysisServices or Microsoft.AnalysisServices.dll


October 29, 2008 1:50 PM

Ells said:

Many thanks.

Just the word reference got me swift results with google. Yep open up the scripttask and click design script. In there on the left hand side is a window called class view (click view - class view) this initially shows the script task object and when you expand it the folder references appears. Right Click on the references folder, choose add. Then in the list Analysis Services Management Objects appears at the top of the list.

Many thanks for the swift response.


October 29, 2008 2:03 PM

christina said:


i have configured all the variables like the database name etc but when i give the partitionID as the new partition_YYYYMM  its giving me an error saying there is no partition with that ID and it does not exist in the collection.

and its not creating a new parition

November 13, 2008 3:48 AM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems