Optimizing Dimension and Cube Processing - SQL Server Analysis Services 2012 Cube Development - page 186

1. Drag Analysis Services Execute DDL Task , found under the Other Tasks
group, to the package's Control Flow tab. Provide a descriptive name for the
task, for example, ProcessDailyPartition..
2. Double-click on the task to launch Analysis Services Execute DDL Task
Editor . Navigate to the DDL tab, and set Connection to the instance of Ana-
lysis Services where you want to process objects.
3. You have multiple options for the Source Type property as follows:
• Direct Input : This option means that you will specify the XMLA com-
mand directly in the SSIS package. This only works if you're confid-
ent that your XMLA will rarely change. If you choose Direct Input
source type, SSIS will allow you to specify the XMLA command in the
Source Direct property.
• File Connection : This option means that you will specify the file con-
taining the XMLA command. This option is more flexible than the Dir-
ect Input option, because you can change the XMLA file as needed
without having to edit the SSIS package.
• Variable : This option is used to specify a variable containing the pro-
cessing XMLA. This is the most flexible option that allows setting a
variable value dynamically, perhaps based on values found in a rela-
tional database table.
4. For this example, choose the File Connection option for Source Type and
specify the full path to the file containing the processing command.
5. To test the task, right-click on it and choose Execute Task .
6. The Execute DDL task could be used to execute any XMLA, not just for pro-
cessing. For example, you could use the same SSIS task type to create new
partitions or synchronize a database from one SSAS instance to another.
Once you're happy with your SSIS package, you could schedule its execution using
the SQL Server Agent job, as follows:
1. Create a new job in SSMS, add a new step, and provide a descriptive name,
such as process SSAS objects .