How to Run the Data Deletion DTS Task

  • Article

You use the Data Deletion Data Transformation Services (DTS) task to delete data from the Data Warehouse. You can use the Data Deletion task to delete detailed log file data, summarized data, all data in the Data Warehouse, and all data for a particular site in the Data Warehouse. For example, you can use the Data Deletion task to delete data from a partially imported log file if the log file import fails.

Make sure that you frequently delete obsolete log file data so that you do not experience declining performance and limited memory capacity in your Data Warehouse. If you want to maintain historic data from your Commerce Server site, you can extract the data for preservation in reports.

Before you run the Data Deletion DTS task, verify that the size of the physical disk where the tempdb database is located is larger than the total size of the database that you want to delete. For example, if the database that you want to delete is 15 GB, SQL Server needs the physical disk where the tempdb database is located to be at least 15 GB. Otherwise, the Data Deletion task may fail.

Note

If you are using SQL Server 2005, you must install DTS 2000 Designer and run the Data Warehouse Import Wizard to create a package. You must have already created a package using the Data Warehouse Import Wizard or have existing packages to import before you can perform this task.

To run the Data Deletion DTS task

  1. If you are using SQL Server 2005, do the following:

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

    2. In SQL Server Management Studio, in the Object Explorer pane, expand the server on which your Data Warehouse is installed, expand Management, and then expand Legacy.

    3. Right-click the package that contains the task that you want to run, and then click Open.

      If the package that you want to open is not in the list, right-click Data Transformation Services, and then click Open Package File or Import Package File. In the Open DTS 2000 Package or Import DTS 2000 Package dialog box, click the package that you have previously saved and want to open or import, and then click Open.

  2. In the DTS Package or DTS 2000 Package Designer dialog box, on the Task menu, click Data Deletion (Commerce Server).

  3. In the Delete Task Properties dialog box, do the following:

    Use this

    To do this

    Description

    Type a description for the task.

    Operation level

    Select from the drop-down list one of the following:

    • Site level to delete Web log file data for a site.

    • Data Warehouse level to delete log file data or all data for a Data Warehouse.

    Site name

    If you selected site level operation, select from the drop-down list the site for which you want to delete Web log data.

    Data Warehouse

    If you selected Data Warehouse level operation, select from the drop-down list the Data Warehouse for which you want to delete Web log data.

    Select delete type

    Select Web Log Imports from the drop-down list to delete data associated with particular Web log data imports.

    Number of retries

    Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.

    Seconds between retries

    Type the number of seconds you want the task to wait between retry attempts. The default time interval is 30 seconds.

  4. In the Delete level section, do the following:

    Use this

    To do this

    Delete all data

    Select this option to delete all data associated with the Web log files that you want to delete.

    The Delete all data option for data imported from a specific Web log file does not delete all the data associated with that Web log file in the Data Warehouse. Instead, it deletes data from all classes that are related to the TaskHistory tables. Related classes have a self, child, parent, grandparent, or grandchild relationship with the members of the TaskHistory table. The classes in the default Data Warehouse schema are as follows:

    • Basket

    • CampaignEvent

    • FirstUriByDate

    • HitsByHour

    • HitsInfo

    • LastUriByDate

    • LogImportJobSummary

    • LogImportSubtask

    • OpenUserVisit

    • Order

    • Request

    • RequestByDateByUriByQueryString

    • Visit

    • VisitInfo

    Delete detailed data

    Select this option to delete the detailed data associated with the Web log files that you want to delete.

  5. In the Logs to delete section, select one of the following from the drop-down list:

    • Delete Web log imports by time. Select this option to specify a time period for which you want to keep imported log file data.

    • Keep a number of current Web log imports. Select this option to specify how many Web log imports for which to keep imported log file data.

    • Select specific Web log imports to delete. Select this option to specify Web log file imports for which to delete imported log file data. Select the box to the left of the Task Id of the Web log file for which you want to delete log file data.

  6. In the Delete Task dialog box, click OK.

  7. On the Package menu, click Execute.

    Note

    Clicking Execute on the Package menu will execute all tasks associated with the selected package. If you want to execute a specific task, for example the data deletion task, right-click Data Deletion (Commerce Server), and then click Execute Step.

    The Executing Package dialog box appears that displays the progress of the import.

  8. In the Package Execution Results dialog box informing you that it completed successfully, click OK.

  9. In the Executing Package dialog box, click Done.

    The detailed data of the selected Web log files is deleted from the Data Warehouse.

See Also

Other Resources

How to Run the Configuration Synchronization DTS Task

Running the DTS Tasks

Importing Data into the Data Warehouse

Managing the Data Warehouse

Commerce Server Operations