Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayData Mining Query Task
Applies to: 
SQL Server
SSIS Integration Runtime in Azure Data Factory
Important
Data mining was deprecated in SQL Server 2017 (14.x) Analysis Services and now discontinued in SQL Server 2022 (16.x) Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.
The Data Mining Query task runs prediction queries based on data mining models built in Analysis Services. The prediction query creates a prediction for new data by using mining models. For example, a prediction query can predict how many sailboats are likely to sell during the summer months or generate a list of prospective customers who are likely to buy a sailboat.
Integration Services provides tasks that perform other business intelligence operations, such as running Data Definition Language (DDL) statements and processing analytic objects.
For more information about other business intelligence tasks, click one of the following topics:
The query is a Data Mining Extensions (DMX) statement. The DMX language is an extension of the SQL language that provides support for working with mining models. For more information about how to use the DMX language, see Data Mining Extensions (DMX) Reference.
The task can query multiple mining models that are built on the same mining structure. A mining model is built using one of the data mining algorithms that Analysis Services provides. The mining structure that the Data Mining Query task references can include multiple mining models, built using different algorithms. For more information, see Mining Structures (Analysis Services - Data Mining) and Data Mining Algorithms (Analysis Services - Data Mining).
The prediction query that the Data Mining Query task runs returns a result that is a single row or a data set. A query that returns a single row is called a singleton query: for example, the query that predicts how many sailboats will be sold during the summer months returns a number. For more information about prediction queries that return a single row, see Data Mining Query Tools.
The query results are saved to tables. If a table with the name that the Data Mining Query task specifies already exists, the task can create a new table, using the same name with a number appended, or it can overwrite the table content.
If the results include nesting, the result is flattened before it is saved. Flattening a result changes a nested result set to a table. For example, flattening a nested result with a Customer column and a nested Product column adds rows to the Customer column to make a table that includes product data for each customer. For example, a customer with three different products becomes a table with three rows, repeating the customer in each row and including a different product in each row. If the FLATTENED keyword is omitted, the table contains only the Customer column and only one row per customer. For more information, see SELECT (DMX).
The Data Mining Query task requires two connections. The first connection is an Analysis Services connection manager that connects either to an instance of Microsoft SQL Server Analysis Services or to an Analysis Services project that contains the mining structure and the mining model. The second connection is an OLE DB connection manager that connects to the SQL Server database that contains the table to which the task writes. For more information, see Analysis Services Connection Manager and OLE DB Connection Manager.
You can set properties through SSIS Designer or programmatically.
Note
The Data Mining Query Editor has no Expressions page. Instead, use the Properties window to access the tools for creating and managing property expressions for properties of the Data Mining Query task.
For more information about how to set these properties in SSIS Designer, click the following topic:
For more information about programmatically setting these properties, click one of the following topics:
Use the Mining Model tab of the Data Mining Query Task dialog box to specify the mining structure and mining model to use.
To learn about implementing data mining in packages, see Data Mining Query Task and Data Mining Solutions.
Name
Provide a unique name for the Data Mining Query task. This name is used as the label in the task icon.
Note
Task names must be unique within a package.
Description
Type a description of the Data Mining Query task.
Connection
Select an Analysis Services connection manager in the list or click New to create a new connection manager.
Related Topics: Analysis Services Connection Manager
New
Create a new Analysis Services connection manager.
Related Topics: Add Analysis Services Connection Manager Dialog Box UI Reference
Mining structure
Select a mining structure in the list.
Mining models
Select a mining model built on the selected mining structure.
Use the Query tab of the Data Mining Query Task dialog box to create prediction queries based on a mining model. In this dialog box you can also bind parameters and result sets to variables.
To learn about implementing data mining in packages, see Data Mining Query Task and Data Mining Solutions.
Name
Provide a unique name for the Data Mining Query task. This name is used as the label in the task icon.
Note
Task names must be unique within a package.
Description
Type a description of the Data Mining Query task.
Data mining query
Type a data mining query.
Related Topics: Data Mining Extensions (DMX) Reference
Build New Query
Create the data mining query using a graphical tool.
Related Topics: Data Mining Query
Parameter Name
Optionally, update the parameter name. Map the parameter to a variable by selecting a variable in the Variable Name list.
Variable Name
Select a variable in the list to map it to the parameter.
Add
Add to a parameter to the list.
Remove
Select a parameter, and then click Remove.
Result Name
Optionally, update the result set name. Map the result to a variable by selecting a variable in the Variable Name list.
After you have added a result by clicking Add, provide a unique name for the result.
Variable Name
Select a variable in the list to map it to the result set.
Result Type
Indicate whether to return a single row or a full result set.
Add
Add a result set to the list.
Remove
Select a result, and then click Remove.
Use the Output tab of the Data Mining Query Task Editor dialog box to specify the destination of the prediction query.
To learn about implementing data mining in packages, see Data Mining Query Task and Data Mining Solutions.
Name
Provide a unique name for the Data Mining Query task. This name is used as the label in the task icon.
Note
Task names must be unique within a package.
Description
Type a description of the Data Mining Query task.
Connection
Select a connection manager in the list or click New to create a new connection manager.
New
Create a new connection manager. Only the ADO.NET and OLE DB connection manager types can be used.
Output table
Specify the table to which the prediction query writes its results.
Drop and re-create the output table
Indicate whether the prediction query should overwrite content in the destination table by dropping and then re-creating the table.
Additional resources
Training
Module
Query a data warehouse in Microsoft Fabric - Training
Learn how to query a data warehouse in Microsoft Fabric using different tools.
Certification
Microsoft Certified: Azure Data Engineer Associate - Certifications
Demonstrate understanding of common data engineering tasks to implement and manage data engineering workloads on Microsoft Azure, using a number of Azure services.
Documentation
-
Dimension Processing Destination - SQL Server Integration Services (SSIS)
Dimension Processing Destination
-
Hadoop Pig Task - SQL Server Integration Services (SSIS)
Hadoop Pig Task
-
Copy Package Objects - SQL Server Integration Services (SSIS)
Copy Package Objects