In the lesson 14, we learned how to create Data Mining queries using the component Data Mining Query Task in SSIS. In this new lesson, we will introduce two SSIS components used to process Data Mining Models:
- Analysis Services Processing Task
- Analysis Services Execute DDL Task
The first one is used to execute objects; the other task is used to execute any XMLA code. You can remove, add or modify objects using the Analysis Services Execute DDL task and you can also process objects.
DDL means Data Definition Language. You can created, modify or delete Data Mining Models using this component. Additionally you can process the objects.
We are using SQL Server 2014 for this lesson, but SQL Server 2005/2008/2012 can be also be used. We are assuming that you already have the Data Mining Project used in Data Mining Part 13. If you do not, you can install it or use any other Data Mining Project already installed.
1. Create a SSIS Project (refer to the lesson 13 if you do not know how to create a SSIS Project).
2. Doble click on the Analysis Services Processing Task.
3. Double click on the Task and press the New button to create a new connection
4. Press the edit button to edit the new connection or select the current configuration and press OK.
5. In the Analysis Services connection manager, select the current connection and press the Add button.
6. In the Add Analysis Services Services, Select the Mining Model decission trees and press OK.
7. Once Selected the objects, press OK.
8. In the steps 1 to 7, we teached you how to process Data Mining Processes using the Analysis Services Processing Tasks. You can execute the task and this will be processed. We can select the Data Mining objects using this task. Another way to do this in SSIS is using the Analysis Services Execution. Drag and drop this task to the design pane.
9. Doble click the Analysis Services Execute Task and Select a Connection and select the Analysis Services Connection and click on the ellipsis button.
10. Copy and paste the XMLA code attached to this article. The XMLA file is the resource files section. For more information about XMA read the XMLA code chapter.
11. You can now Execute the Task to process the Data Mining Models.
In this lesson, we learned how to use the Analysis Services Processing Task and the Analysis Services Execute DDL task. The first one is used to process the The Data Mining Models and Structures. The Analysis Services Execute DDL task allows you to execute any XMLA sentence including the processing tasks.