Introduction
In the chapter 14, we learned about the Data Mining Query Transformation Task in SSIS. In chapter 15, we learned to use the SSIS Analysis Services Processing Task. This new chapter will show how to work with the Data Mining Query Transformation Task.
This SSIS task is used to predict data using existing models, with a data source provided, and send the result to any output format (Excel, Flat file, Oracle, any ODBC connection, etc). In this example, we will create a DMX query and send the results to a flat txt file.
Requirements
The following requirements are needed for this example.
- SSIS Installed.
- SSAS Installed.
- SQL Server Installed.
- SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio).
- The AdventureworksDW Database.
Getting Started
1. In SSDT, go to File>New Project.
2. Select the Integration Services Project.
3. Drag and drop the Data Flow Task to the design pane and double click on it.
4. In the Data Flow drag and drop the OLE DB Source, the Data Mining Query task and the Flat File Destination.
5. Double click on the OLE DB connection manager and press the New button.
6. In the Configure OLE DB Connection Manager, press the New button.
7. Select the Native OLE DB\SQL Server Native Client 11.0 and select the SQL Server Name and the database name. In this example, we will use the Adventureworks2014 database.
8. Select the dbo.vTargetMail.
9. Save the changes and double click on the Data Mining Query Transformation. Press the New button.
10. Press the edit button.
11. Select the Native OLE DB\Microsoft OLE DB Provider for Analysis Services. Select the SQL Server Name and the Windows NT Security. Also, select the Adventureworks database.
12. Select the Targeted Mailing Mining Structure and the TM Decision Tree mining model.
13. In the Query tab, press the Build New Query button.
14. In the Source section select the Data Flow Input Column 5 times and specify the fields and criteria arguments as shown in the picture.
In this query, we are selecting a customer who bought articles (is bikebuyer=1), is married (Marital Status='M'), has a gender of male (gender = 'm'), a total number of children = 1, and their English education is high school. 
15. The Data mining DMX query will be displayed. You can check the code created visually.
16. We will store the results in a flat file. To do that, once the Data Mining Query Transformation task is done, double click on the Flat File Destination and press the New button.
17. Select the Delimited format.
18. Specify the path in the file name section. The file will be created automatically if it does not exists.
19. Press the Columns page.
20. Click the Mappings page options.
21. Run the package. You will be able to see the results in a txt file.
22. The txt file will show all the customers that accomplish the query specified on step 14.
Conclusion
In this example, we show how to use the Data Mining Query Transformation task to obtain results from queries and store the results in a flat file.
References
For more information, refer to the following link:
