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
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure
Works DW Standard Edition</DatabaseID>
</Object>
<File>D:Program
FilesMicrosoft SQL ServerMSAS10.SQL2008OLAPBackupAdventureWorksDW.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
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.
Conclusion
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.
]]>