Using Analysis Services Processing Task & Analysis Services Execute DDL Task in SSIS
Configuring the Analysis Services Execute DDL Task
- 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”.
- 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.
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
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
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
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