Konstantin Gorakine's BI Lingual Blog

As you can see, there are just a few dynamic parts that can be replaced on the fly to make partition script fully dynamic, the list is highlighted: Partition ID, Partition Name, WHERE clause and Partition Slice. SSIS XML Task Merge operation can add new tabs to a XML document, so basically, idea here is to add all dynamic parts in the run time and send the final script to the SSAS for execution. Let’s try to do that. First, we create a Master XMLA partition script template by deleting all dynamic tags and store it somewhere in the share folder. So, the Master script template will look like this:

As you can see, there is no Partition ID, Name or Slice tags in it, as well, WHERE clause got modified by replacing actual Year value with "####" mask. Additionally, <Create> tag had xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" attribute, but looks like XML Task can’t read the entire XML when this attribute is present, so I was forced to remove it while building dynamic XMLA with XML Task and restore it right before sending final XMLA script to the SSAS, since SSAS won’t process <Create> command without it. Next, I built a simple SSIS package with 5 tasks and 5 variables.

Package Variables are set up and configured in the following way

For example, Node_PartiitonID variable’s Expression property is set to "<ID>Transaction_" + @[User::CurrentYear] + "</ID>" to build partition ID based on the current Year.

[Add Partition ID] XML Task is configured as follows and self-explanatory.

Here, I connected to my Master XMLA template, went to /Create/ObjectDefinition/Partition XPath and inserted value taken from [User::Node_PartitionID] variable. After all this, I saved results of generated XMLA script to [User::XMLA] variable. In the next two tasks, I am sequentially adding Partition Name and Partition Slice to a in-variable script, each time modifying and saving final version of XMLA variable contents. Configuration for the [Add Partition Slice] looks like this.

Here, source is [User::XMLA] variable of course, not initial version of the Master.xmla file. The task before the last one is the Script Task. We need to add some final details to the almost-ready-for-the-run XMLA script. Couldn’t avoid using .NET completely, J

      Public Sub Main()

        ‘year mask

        Const YEAR_MASK As String = "####"

        ‘get current year

        Dim sCurrentYear As String = _

             Dts.Variables("User::CurrentYear").Value.ToString

        ‘replace #### mask with current year value

        Dim sXML As String = _

             Dts.Variables("User::XMLA").Value.ToString.Replace(YEAR_MASK, sCurrentYear)

        ‘add before droped xmlns attribute back to <Create> tag

        sXML = sXML.Replace("<Create>", _

             "<Create xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">&quot;)

        ‘store modified script back to var

        Dts.Variables("User::XMLA").Value = sXML

        Dts.TaskResult = Dts.Results.Success

      End Sub

The last task sends our final XMLA script to the SSAS server.

All this framework (5 tasks) can be placed inside a For Each Loop container, which will loop over all years that require new partition creation. The logic on how to find these years may be implemented differently, depending on overall system requirements and SSAS version. This can be done by reading a list of existing partitions from DMV, or SSIS can be configured not to fail if partition already exists, etc.

Of course, this solution has it’s own drawbacks: partitions can be created sequentially only and not in parallel, but if requirements for partition creation are moderate, like in our case, then it’s good enough. Another one is a maintenance of the Master XMLA template, but my answer to this: if DBA’s are already managing thousands of scripts, then one or few more is not a big deal at all J. Nonetheless, the main demand is achieved: no or little of .NET "nightmare".