Using Analysis Services Processing Task & Analysis Services Execute DDL Task in SSIS

Configuring the Analysis Services Execute DDL Task

  1. Drag and drop the “Analysis Services Execute DDL Task” from the toolbox to the Control Flow window and rename the task as “Backup Analysis Services Database Using – Analysis Services Execute DDL Task”.
  2. Double click the Analysis Services Execute DDL Task to open up the Analysis Services Execute DDL Task Editor and then navigate to DDL page and specify the values for Connection, SourceType and SourceDirect options.
  3. In the connection drop down you can choose the connection which you have already created in the previous task to connect to the Analysis Services Database. The three different options which are available under SourceType are Direct Input, File Connection and Variable. In this example, you need to choose Direct Input as the value for the SourceType option. Next, you need to specify the below mentioned  data definition language (DDL) statement in SourceDirect option and click OK to save the DDL Statements window.

XMLA Script to Backup Analaysis Services Database

<Backup xmlns=””>


    <DatabaseID>Adventure Works DW Standard Edition</DatabaseID>


  <File>D:Program FilesMicrosoft SQL ServerMSAS10.SQL2008OLAPBackupAdventureWorksDW.abf</File>



This completes the configuration of the Analysis Services Execute DDL Task and it will look similar to the snippet shown below.

Click OK to save the Analysis Servers Execute DDL Task.

Finally, to complete the example we will connect the Analysis Services Processing and Analysis Services Execute DDL tasks together. The backup of the Analysis Services Database after the successful  execution of this task will be available in the default analysis services backup location i.e. in “Program FilesMicrosoft SQL ServerMSAS10.SQL2008OLAPBackup”.

Execute the package by right clicking the AnalysisServicesTasks.dtsx package from the Solution Explorer and then selecting Execute Package option from the drop down list.

After the successful execution of the SSIS package, the analysis services database backup will be available in the default location as shown in the snippet below.


In this article you have seen how to configure and use the Analysis Services Processing Task and the Analysis Services Execute DDL Tasks which is available in SQL Server 2005 Integration Services and later versions.

Pages: 1 2


No comments yet... Be the first to leave a reply!