Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services SSIS Analysis Services Execute DDL Task


By:   |   Updated: 2008-08-07   |   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.


Last Updated: 2008-08-07


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 05, 2017 - 10:16:13 PM - Mazhar Back To Top

 

 Hi there,

In my Internship project, I am developing SSAS tabular model to connect Power BI, As a project requirment every week I have to drop recorods of last two weeks and update for last three weeks alos automate same process using SSIS

can any please guide me

Thanks.

Mazhar


Friday, August 03, 2012 - 9:52:50 AM - Nik - Shahriar Back To Top

I am tring to runthe mentioned XML in SSIS and get the return and display it in a Table format , how can i do that in SSIS

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions>
    <RestrictionList>
      <ObjectExpansion>ObjectProperties</ObjectExpansion>
    </RestrictionList>
  </Restrictions>
  <Properties>
    <PropertyList>
      <Format>Tabular</Format>
    </PropertyList>
  </Properties>
</Discover>

 

Thanks

Nik


Thursday, February 10, 2011 - 2:48:45 PM - Alberto Back To Top

muchas gracias....!!!!!!!!!!!


Wednesday, July 14, 2010 - 4:44:50 PM - Amir K. Back To Top

Hi,

Thanks for sharing the knowledge. I would like to build cube DBs in different servers; How to configure the cube DB name and data source (DS) information when creating cube DBs using "Analysis Services Execute DDL Task" in SSIS package at runtime?

 Thanks,

Amir.


Learn more about SQL Server tools