How to use profiling services to perform DDL tasks in SQL Server 2005

Problem:

We look forward to automating tasks in our SQL Server Analysis Services server. Would you please explain to us how to use the profiling service in SSIS to perform DDL tasks (Analysis Services Execute DDL Task) details?

Expert Answer:

The Profiling service performs DDL tasks (Analysis Services Execute DDL Task) is a useful tool that allows you to do anything with a SQL Server Analysis service instance. For example, you can back up a database, process a data analysis cube, and create a partition. Use XML parsing (XMLA) to execute the specified command, which is a native XML protocol used by the client application to interact with the Microsoft SQL Server Analysis Service instance. You can find all the details about XMLA on the Internet or in books, just search XMLA.

One thing to keep in mind is that you can use SQL Server Management Studio (SSMS) to create whatever scripts you need to do. For example, you can connect to the server for SQL Server Analytics services, right-click a database, and choose back up from the Drop-down menu. You can click the Script button in the BACKUP database session to generate a XMLA script to perform the backup. You can run this XMLA script from the SSIS package using the profiling service to perform the DDL task. The benefit of creating SSIS packages is that you can perform a repeatable process through the SQL Server agent as needed or as scheduled.

In this approach, we follow the steps in an SSIS package to perform the DDL task (Analysis Services Execute DDL tasks) using the profiling service. We will create a sample package to perform the backup of the Adventure Works DW Analytics Service database with SQL Server 2005.

To create a sample SSIS package

Start Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group and create a new integrated service project (Integration Services Project). An SSIS package named PACKAGE.DTSX is automatically generated and added to the project. Rename the package (package) to SSASEXECUTEDDLTASK_DEMO.DTSX, and then proceed to the following steps in the SSIS package:

Step One:

Add a connection management (Connection Manager) to the SSAS server. Right-click on the connection management (Connection managers) page and select a new analysis service connection from the Drop-down menu (A/Connection). Accept the default value in the dialog box to connect to the local SSAS server (if you want to connect to an SSAS server on another machine, you can edit it as appropriate).

Step Two:

Add a string variable to the package (package). We can see that this variable contains the XMLA script to perform the backup. Right-click Control flow, select the variable in the Drop-down menu, and then enter the variable as follows:

Step Three: