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=””>


Works DW Standard Edition</DatabaseID>


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!