I’ve been working on a way to process SSAS Tabular Partitions using SSIS. At the current company I’m working at we need to use SSIS to process our tabular models weather it’s a full process on the database or just a partition. This blog focuses on processing a partition because I only want to process the most current data to my model so I base the partition on a date range. I’m using the Adventure Works 2012 tabular model for my example in this article. I want to focus on 2 methods of which either one can be used it’s just based on your comfort with the technology.
The 2 SSIS Methods to process the tabular Model:
- C# Script using AMO
- SSIS Analysis Services Execute DDL Task
The first thing I will mention is that you need to look at how the SSAS Tabular Partition is processed manually to see how you can use these 2 methods in SSIS.
1. Get the XMLA (XML for Analysis) from SQL Server Management Studio. To access the SSAS Tabular instance you right click on the table with the partition and select Partitions.
Select the Partition you want and the Process button.
Select the Process Mode in our case you want to Process Full then select the Script option and choose the Script Action to New Query Window.
This gives us the XMLA script that you can execute to process the partition selected.
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Type>ProcessFull</Type>
<Object >
<DatabaseID>AdventureWorks Tabular Model SQL 2012</DatabaseID>
<CubeID>Model</CubeID>
<MeasureGroupID>Internet Sales_fdac9a13-4019-4773-b193-7cca3a4883eb</MeasureGroupID>
<PartitionID>Internet Sales_eda26cf6-7bda-42ad-b31b-d7dc3ec08e6f</PartitionID>
</Object >
</Process>
2. Create a C# script within SSIS that will allow you to generate the XMLA script and add variables so that it can be dynamic.
Create several variables to allow us to make dynamic changes to the script because you may change databases, models, or partitions.
In our script task you want to add the ServerID, DatabaseID, CubeID, PartitionID, Type, and MeasureGroupID to the ReadOnlyVariables. You also want to add the XMLA variable to the ReadWriteVariables.
The following is a code snippet of the XMLA generated dynamically from the variables in a C# script and sent to the XMLA variable.
public void Main()
{
try
{
StringBuilder xmla = new StringBuilder();
xmla.AppendLine("<Process xmlns=" + "\"http://schemas.microsoft.com/analysisservices/2003/engine\"" + ">");
xmla.AppendLine("<Type>" + Dts.Variables["Type"].Value.ToString() + "</Type>");
xmla.AppendLine("<Object >");
xmla.AppendLine("<DatabaseID>" + Dts.Variables["DatabaseID"].Value.ToString() + "</DatabaseID>");
xmla.AppendLine("<CubeID>" + Dts.Variables["CubeID"].Value.ToString() + "</CubeID>");
xmla.AppendLine("<MeasureGroupID>" + Dts.Variables["MeasureGroupID"].Value.ToString() + "</MeasureGroupID>");
xmla.AppendLine("<PartitionID>" + Dts.Variables["PartitionID"].Value.ToString() + "</PartitionID>");
xmla.AppendLine("</Object >");
xmla.AppendLine("</Process>");
Dts.Variables["XMLA"].Value = xmla.ToString();
}
catch (Exception e)
{
Dts.Variables["ScriptError"].Value = e.Message.ToString();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
The idea is that you use the StringBuilder library to design our same XMLA script but you add the all the variables in the right places and send it the XMLA variable for use in the next piece.
3. Method 1: C# Script using AMO
With the XMLA script above created you just need a way to execute it. First you need to add the Microsoft.AnalysisServices.XMLA assembly to your script references so you can access the Microsoft.AnalysisServices.Xmla namespace. This can be a pain in the butt to find but I found it on my machine under C:\windows\assembly\gac_msil\microsoft.analysisservices.xmla and I picked the latest version of the microsoft.analysisservices.xmla.dll.
Finally you just add to the script we used to generate the XMLA script and execute it
with AMO commands. You instantiate the XmlaClient and connect with the Server
variable. Then you execute the send command and disconnect from the server.
public void Main()
{
try
{
StringBuilder xmla = new StringBuilder();
xmla.AppendLine("<Process xmlns=" + "\"http://schemas.microsoft.com/analysisservices/2003/engine\"" + ">");
xmla.AppendLine("<Type>" + Dts.Variables["Type"].Value.ToString() + "</Type>");
xmla.AppendLine("<Object >");
xmla.AppendLine("<DatabaseID>" + Dts.Variables["DatabaseID"].Value.ToString() + "</DatabaseID>");
xmla.AppendLine("<CubeID>" + Dts.Variables["CubeID"].Value.ToString() + "</CubeID>");
xmla.AppendLine("<MeasureGroupID>" + Dts.Variables["MeasureGroupID"].Value.ToString() + "</MeasureGroupID>");
xmla.AppendLine("<PartitionID>" + Dts.Variables["PartitionID"].Value.ToString() + "</PartitionID>");
xmla.AppendLine("</Object >");
xmla.AppendLine("</Process>");
Dts.Variables["XMLA"].Value = xmla.ToString();
XmlaClient clnt = new XmlaClient();
clnt.Connect(Dts.Variables[&quot;ServerID&quot;].Value.ToString());
clnt.Send(xmla.ToString(), null);
clnt.Disconnect();
}
catch (Exception e)
{
Dts.Variables["ScriptError"].Value = e.Message.ToString();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
This is one way of processing the partition and a preferred preference if you feel comfortable with C# and Analysis Management Objects.
4. Method 2: Analysis Service Execute DDL Task
This method uses the SSIS task designed to execute XMLA scripts. Again you are using the original C# script to generate the XMLA Script. You simply make a connection to the SSAS Tabular instance via a connection string. You add the XMLA variable to the DDL command so that we pass in the generated XMLA script to the SSIS task.
Once you add the connection string and the XMLA variable set in the task you are good to go. You have a second method to process the XMLA Script using a SSIS Analysis Service Execute DDL Task.
jessekraut 