ETL Processing for DTS Tasks
You use Commerce Server Data Transformation Services (DTS) tasks to import and change data in the Data Warehouse. The Commerce Server tasks are loaded into SQL Server when you unpack a site. You add the Commerce Server tasks to a SQL Server package and then run that package to change the Data Warehouse.
You can manually configure the Commerce Server DTS tasks to import data, or you can use the Data Warehouse Import Wizard. For more information about the wizard, see How to Start the Data Warehouse Import Wizard.
You can also use scripts to run the Commerce Server DTS tasks. Besides using the Commerce Server DTS tasks to import Commerce Server data into the Data Warehouse, you can extend the logical schema and the functionality of the Data Warehouse to import custom data.
For information about how to create new DTS tasks, see SQL Server Books Online.
Commerce Server DTS tasks extract, transform, and load (ETL) data for reporting purposes in the Data Warehouse. The ETL process starts when data is extracted from specific data sources. The data is then transformed by using rules, algorithms, concatenations, or any number of conversion types, into a specific state. 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 that contains 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. Notice that because 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 it is required, to the destination data type. |
Uppercase String |
Use to convert a source column to all uppercase characters and, if it is required, 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 in the following list 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 following sections because the SiteName is always extracted from the same location for all DTS tasks.