SQL Server Integration Services Analysis Services Execute DDL Task

By: Ray Barley   |   Comments (4)   |   Related: More > Integration Services Analysis Services Tasks

Problem
We are looking to automate some tasks to be performed on our SQL Server Analysis Services Servers.  Can you give us the details on how the Analysis Services Execute DDL Task can be used in a SQL Server Integration Services (SSIS) package?

Solution
The Analysis Services Execute DDL Task is a very useful one, allowing you to do just about anything with a SQL Server Analysis Services instance.  For example you could backup a database, process a cube, create a partition, merge partitions, etc.  You specify commands to be executed using XML for Analysis (XMLA) which is the native XML protocol for all interaction between a client application and a Microsoft SQL Server Analysis Services instance.  You can find all of the details about XMLA in Books on Line; just search on XMLA.

A key point to keep in mind is that you can use SQL Server Management Studio (SSMS) to generate a script for just about anything you need to do.  For instance you can connect to a SQL Server Analysis Services server, right click on a database, then select Back Up from the context menu.   You can then click the Script button on the Backup Database dialog to generate the XMLA script to perform the backup.  You can run this XMLA script from an SSIS package by using the Analysis Services Execute DDL Task.  The benefit of creating the SSIS package is that you now have a repeatable process that you can run on demand or schedule via SQL Server Agent. 

In this tip we will walk through the steps to use the Analysis Services Execute DDL Task in an SSIS package.   We'll create a sample package that will perform a backup of the Adventure Works DW Analysis Services database that comes with SQL Server 2005.  

Create the Sample SSIS Package

To begin launch Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group and create a new Integration Services project.   An SSIS package named Package.dtsx will be created automatically and added to the project.  Rename the package to SSASExecuteDDLTask_Demo.dtsx then perform the following steps on the SSIS package: 

Step 1: Add a Connection Manager for the SSAS server.  Right click in the Connection Managers area and select New Analysis Services Connection from the context menu.  Accept the defaults in the dialog to connect to the local SSAS Server (or edit as appropriate if you want to connect to an SSAS Server on another machine):

Step 2: Add a string variable to the package; we will use this variable to contain the XMLA script to perform the backup.  Right click on the Control Flow, select Variables from the context menu, then enter the variable as follows:

Step 3: Drag and drop the Script Task from the Toolbox onto the Control Flow of the SSIS package.  Edit the Script Task and add the package variable created in Step 2 above to the ReadWriteVariables property.  We will assign the XMLA script to this variable in the next step.   

Step 4: Click the Design Script button in the Script Task Editor and enter the following XMLA script (remember you can generate the script using SSMS):

Public Sub Main()
  Dim backupfilename As String = "AdventureWorksDW_" + Now().ToString("MMddyyyy") + ".abf"
  Dim xml As String = _
      "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" + _
      "<Object>" + _
      "   <DatabaseID>Adventure Works DW</DatabaseID>" + _
      "</Object>" + _
      "<File>${BACKUPFILENAME}</File>" + _
      "</Backup>"
  Dts.Variables("User::v_XMLA").Value = xml.Replace("${BACKUPFILENAME}", backupfilename)
  Dts.TaskResult = Dts.Results.Success
End Sub

This is just an example of how you might fine tune the XMLA that you generate with SSMS.  The backup file name is modified to include the current date.  The resulting XMLA is stored in the package variable named v_XMLA.  The use of ${BACKUPFILENAME} for the text to replace is purely arbitrary, but hopefully intuitive.

Step 5: Drag and drop the Analysis Services Execute DDL Task from the Toolbox onto the Control Flow of the SSIS package and connect it to the Script Task configured earlier.  Open the Analysis Services Execute DDL Task editor, click on DDL in the list box on the left, and set the properties as follows:

The XMLA to execute is defined in the package variable that we setup in the previous step.

At this point the SSIS package will look like this:

At this point you can execute the SSIS package and you will see the backup file created; the default location is specified in the BackupDir property for the Analysis Server; e.g. C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup. 

Next Steps

• If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Enterprise folder.
• Take a look at the Books on Line topic XMLA to review all of the possible commands that you can execute via the Analysis Services Execute DDL Task.
• You can download the sample SSIS project created in this tip here.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips