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

SELECT FLATTENED

  t.[FullName],

  t.[EmailAddress],

  t.[Gender],

  t.[MaritalStatus],

  t.[YearlyIncome],

  t.[NumberCarsOwned],

  [TM Decision Tree].[Bike Buyer]

From

  [TM Decision Tree]

PREDICTION JOIN

  OPENQUERY([Adventure
Works DW],

    ‘SELECT

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

      [EmailAddress],

      [Gender],

      [MaritalStatus],

      [YearlyIncome],

      [NumberCarsOwned],

      [TotalChildren],

      [NumberChildrenAtHome],

      [HouseOwnerFlag],

      [CommuteDistance]

    FROM

      [dbo].[DimCustomer]

    ‘) AS t

ON

  [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]

WHERE

  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.

Conclusion

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.

]]>

Leave a comment

Your email address will not be published.