How to Run the Data Deletion DTS Task

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

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.

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. If you are using SQL Server 2000, do the following:

   1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

   2. In the SQL Server Enterprise Manager console, in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

   3. Right-click Data Transformation Services, and then click New Package.

      Alternatively, if you are changing an existing package, right-click Data Transformation Services, and then click Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

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

4. 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.

5. 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.

6. 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.

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

8. On the Package menu, click Execute.

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

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