Extract, Transform, and Load Process for DTS Tasks
This section contains reference information for the extract, transform, and load (ETL) process for the Commerce Server DTS tasks. The ETL process begins when data is extracted from specific data sources. The data is then transformed, using rules, algorithms, concatenations, or any number of conversion types, into a specific state. Once in this state, the transformed data is loaded into the Data Warehouse where it can be accessed for use in analysis and reporting.
The following table describes the types of transformations that the DTS tasks apply to data.
| Transformation | Description |
|---|---|
| Copy Column | Use to copy data directly from source to destination columns, without any transformations applied to the data. |
| Copy Cell | Use to copy data directly from source to destination cells, without any transformations applied to the data. |
| Concatenation | Use to consecutively link strings from more than one source column into one string containing a semi-colon separated list of strings, and copy the result to the destination column. |
| Generated | Use when the DTS task creates new data, or summarizes data from the source. |
| ActiveX Script | Use to build custom transformations. Note that since the transformation occurs on a row-by-row basis, a Microsoft ActiveX script can affect the execution speed of a DTS package. |
| DateTime String | Use to convert a date or time in a source column to a different format in the destination column. |
| Lowercase String | Use to convert a source column to all lowercase characters and, if necessary, to the destination data type. |
| Uppercase String | Use to convert a source column to all uppercase characters and, if necessary, to the destination data type. |
| Middle of String | Use to extract a substring from the source column, transform it, and copy the result to the destination column. |
| Trim String | Use to remove leading, trailing, and embedded white space from a string in the source column, and copy the result to the destination column. |
| Read File | Use to open the contents of a file whose name is specified in a source column, and copy the contents into a destination column. |
| Write File | Use to copy the contents of a source column (data column) to a file whose path is specified by a second source column (file name column). |
Most tables in the Data Warehouse contain columns named TableID and TableInternalFlag, where Table is the name of the Data Warehouse table in which the column appears. These columns are not listed in the tables in the sections for each task listed below because the columns are not loaded by the DTS tasks, they are loaded by the OLE DB Provider for Commerce Server. The TableID column contains a binary six-byte unique identifier. The TableInternalFlag column is for internal use only, and is used by the OLE DB Provider for Commerce Server.
Most Data Warehouse tables also contain a SiteName column. This column, like TableID and TableInternalFlag, is also not listed in the tables in the sections below because the SiteName is always extracted from the same location for all DTS tasks.
This section contains:
- ETL Process for the Configuration Synchronization DTS Task
- ETL Process for the Web Server Log Import DTS Task
- ETL Process for the Transaction Data Import DTS Task
- ETL Process for the Product Catalog Import DTS Task
- ETL Process for the Profile Data Import DTS Task
- ETL Process for the Campaign Data Import DTS Task
- ETL Process for the Data Deletion DTS Task
- ETL Process for the Model Builder DTS Task
- ETL Process for the IP Resolution DTS Task
- ETL Process for the Report Preparation DTS Task
- ETL Process for the Report Caching DTS Task
Copyright © 2005 Microsoft Corporation.
All rights reserved.