Using The Data Mining Query Task in SSIS

14.      In the Query Tab you will now have the DMX query which is generated based on the selections which you had made in the Build New Query window along with the criteria/arguments (the generated DMX query is also show  below for your reference). You can leave rest of the child tabs of Query tab unmodified.

Generated DMX Query








  [TM Decision Tree].[Bike Buyer]


  [TM Decision Tree]


  OPENQUERY([Adventure Works DW],


      (CASE      WHEN MiddleName IS NULL THEN          FirstName + ” ” + LastName     ELSE          FirstName + ” ” + MiddleName + ”.” + ” ” + LastName END) AS [FullName],












    ‘) AS t


  [TM Decision Tree].[Marital Status] = t.[MaritalStatus] AND

  [TM Decision Tree].[Gender] = t.[Gender] AND

  [TM Decision Tree].[Yearly Income] = t.[YearlyIncome] AND

  [TM Decision Tree].[Total Children] = t.[TotalChildren] AND

  [TM Decision Tree].[Number Children At Home] = t.[NumberChildrenAtHome] AND

  [TM Decision Tree].[House Owner Flag] = t.[HouseOwnerFlag] AND

  [TM Decision Tree].[Number Cars Owned] = t.[NumberCarsOwned] AND

  [TM Decision Tree].[Commute Distance] = t.[CommuteDistance]


  t.[Gender] = ‘M’ AND

  t.[MaritalStatus] = ‘M’ AND

  t.[YearlyIncome] > 80000 AND

  t.[NumberCarsOwned] > 1 AND

 [TM Decision Tree].[Bike Buyer] = 1

15.      In the Data Mining Query Task Editor window select the Output tab and click the New button to open the Configure ADO.Net Connection Manager and then click New… to configure the Connection Manager as shown below.

16.      Click OK to save the changes in the Connection Manager and to return to Configure ADO.Net Connection Manager screen, here you need to once again click OK to return to parent Data Mining Query Task Editor Window.

17.      In Data Mining Query Task Editors Output tab you will need to provide the output table name ( BikeBuyers in this example) and select   “Drop and re-create the output table” as shown below . This will drop and recreate the table every time this SSIS task is used. Finally, click OK to save and complete the configuration of Data Mining Query Task.

18.      Next, execute the package by right clicking the DataMiningQueryTask.dtsx package from the Solution Explorer and then select Execute Package option from the drop down list.

19.      After the successful execution of the SSIS package, the Data Mining Query Tasks output will be available in dbo.BikeBuyers table of TempDB database as shown below.


In this article you have seen how easily you can configure and use Data Mining Query Task which is available in SQL Server 2005 Integration Services and later versions.

Pages: 1 2


No comments yet... Be the first to leave a reply!