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.
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
If you are using SQL Server 2005, do the following:
Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
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.
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.
If you are using SQL Server 2000, do the following:
Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
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.
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.
In the DTS Package or DTS 2000 Package Designer dialog box, on the Task menu, click Data Deletion (Commerce Server).
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.
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.
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.
In the Delete Task dialog box, click OK.
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.
See Also
Other Resources
How to Run the Configuration Synchronization DTS Task