Introduction
In this chapter, we will have a brief introduction to SQL Server Integration Services (SSIS) and the Data Mining Query Task Component.
SSIS is a component included in SQL Server to integrate different databases with Excel, XML, Web Services, C#, VB Code. In this lesson, we will show how to save the data mining results in a SQL Server table using SSIS.
The Data Mining Query Task is a visual task created to create data mining queries and export the results to SQL Server or Oracle or Excel or any other database. With SSIS you can easily interact between heterogeneous databases.
Requirements
We are using SQL Server 2014 for this lesson, but the SQL Server 2005/2008/2012 and 2014 can be also be used.
We are assuming that you already have the Data Mining Project used in the Data Mining Part 13. If you do not, you can install it or use any other Data Mining Project already installed.
Getting Started
1. You can use the Visual Studio to open a SSIS project.
2. In the Menu, go to File>New Project and choose Integration Services Project. Specify the Name and Path.
3. There are many tasks in SSIS to work with Web Services, create backups, execute queries, etc. In this lesson, we are going to use the Data Mining Query Task. Double click it.
4. In the design pane, you will see the Task. Double click it.
5. We need to connect to the Data Mining. Press the New button to create a new Connection.
6. Press the New Button to create a connection.
7. In the Provider Combobox Select the Provider for Analysis Services. In the Server or file name, specify the name of the server and in the Initial Catalog select the database with the Data Mining Model. In this case, we are using the database used in chapter 13.
8. Once you have selected the data connection, press OK.
9. Select a Mining Structure and a Mining Model and Click on the Query Tab.
10. Here you can create your Data Mining Query visually. We are going to create a query here. Press the select model button.
11. I am not going to explain in detail how to create queries here. You can refer to the Data Mining Introduction for more details, Predict the future, steps 1 to 8. You will specify the customer characteristics of the Data Mining Query. With that information, create a query similar to the picture.
12. Once finished, you can observe the DMX query generated. For more information about DMX, go to the lesson 12. Click in the Output tab.
13. In the output table, write test1. This is the table used to store the Data Mining Queries.
In the Connection press the New Button.
14. Specify the Provider. In this case we will store the information in SQL Server. That is why we are using the SqlClient Data Provider, but we could store the information in Oracle or other Database. Select the database server name and select or enter the database name. In this sample, we will work with the AdventureWorks database. Press OK.
15. Now, right click on the task and select Execute Task.
16. If everything is OK, you can open the SQL Server Management Studio and check in the AdventureWorks if the new table named dbo.
17. Verify the data in the Table.
Conclusions
As you can see, you can easily export your Data Mining Query results in a SQL Server table in Oracle or any other database using the SSIS components.