SQL Server Analysis Services Execute DDL and Processing Tasks

The SQL Server Analysis Services Execute DDL task is the Analysis Services equivalent of the Execute SQL task, but it is not quite as robust. The task simply executes a DDL statement against an Analysis Services system. The results of the DDL statement cannot be outputted to a variable as we showed in the Execute SQL task. Typically you would use something like DDL statements to create a cube, a dimension, or any other OLAP object.

To configure the task, go to the DDL page and select the Connection Manager that you wish to execute the DDL statement against in the Connection option. Then select whether the DDL statement will be directly inputted, pulled from a file, or pulled from a variable in the SourceType option. The last option is dynamic and will vary based on what you selected for the source of the DDL statement in the previous option. Essentially that option selects the variable or file where the DDL statement is stored or allows you to directly input the statement.

The SQL Server Analysis Services Processing task takes care of the processing of Analysis Services objects. The configuration of the task is done in the Analysis Services Processing Task Editor in the Analysis Services page. First, select the Analysis Services Connection Manager that you wish to process. Next, click the Add button and select the Analysis Services objects you'd like to process. After clicking OK, you'll be taken back to the Analysis Services page (Figure 3-25), where you can change the type of processing you will be performing. To do this, right-click on each object and select the process option you'd like. The option varies based on the type of object.

Figure 3-25

If you click Impact Analysis, analysis is performed on the selected objects, showing you the objects that will be affected by the processing. The Change Settings button lets you configure the batch settings for the task. For example, here you can change whether you want the objects to be processed in sequential order or in parallel. You also can specify how you want errors handled.