SSIS: Create Analysis Services partitions from a SSIS package

Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports 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 FunctionEnd Class