Running the Transaction Data Import DTS Task

Article
11/12/2009

You use the Transaction Data Import DTS task to import site transaction data into the Data Warehouse. Transaction data is stored in the Commerce Server transaction database. It includes purchase orders and requisition numbers, which are used in analysis reports to determine how well individual products are selling, how much is being requisitioned, or how much your customers are purchasing.

Notes

Strings that are stored as a global unique identifier (GUID) must begin and end with curly brackets “{” and “}”.This is the case with address_id, which is processed by the Transaction data import task. Make sure you insert the beginning and ending curly bracket before writing an event to the Web log file.

For example, your site must generate the GUID in the Commerce event of the type CEVT={T=ORD,EVT=SUBOR,ORID="fd5823ed-cac9-4b29-aa82-a18fe43b78e8"} as {fd5823ed-cac9-4b29-aa82-a18fe43b78e8}. The CSOLEDB provider does not convert the GUID if the required beginning and ending curly brackets are missing.

The Retail Solution Site uses the MSCSGenID object to create a GUID with curly brackets, for example:
```
Set Application("MSCSGenID") = oGetGenIDObject()
Function oGetGenIDObject() Set oGetGenIDObject = Server.CreateObject("Commerce.GenID") End Function
```
The Retail 2002 site uses the SYSTEM.GUID object, and then formats the string to add the GUID, for example:
```
Accountmanager.cs string profileID = string.Format("{0}{1}{2}", '{', guidString, '}');
public static string GenerateProfileID()
        {
            //
            // Create a guid. In .net this does not contain the curly brackets.
            //
              Guid guid = Guid.NewGuid();
           //
           // The profile service takes ID's as strings (and stores them nvarchar not as uniqueidentifier)
          //
              string guidString = guid.ToString();
          //
          // Add in the curly bracket.
          //
            string profileID = string.Format("{0}{1}{2}", '{', guidString, '}');
            return profileID;
        }
```
You must use the full load option and perform a full import the first time you import transaction data into the Data Warehouse. After you have imported transaction data, use the incremental load option to import only new data that was created since you last imported transaction data.
When running the Transaction Data Import DTS task on a Data Warehouse that contains data for multiple Web sites, ensure that classes extended by more than one Web site have uniquely named members if their data types are not the same. If a new member is added to a class in multiple Web sites and the data types of the new members are not the same, a failure can occur or data can be incorrectly converted when the Transaction Data Import DTS task is run.

To import transaction data for a single site into your Data Warehouse

To import transaction data for all associated sites into your Data Warehouse

To import transaction data for a single site into your Data Warehouse

Run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse. For instructions, see Running the Configuration Synchronization DTS Task.
Expand Microsoft SQL Server, 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, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.
On the Task menu, click Transaction data import (Commerce Server).

In the Transaction Import Properties dialog box, on the General tab, do the following:

Use this	To do this
Description	Type a description for this import task.
Operation level	Select Site level from the drop-down list.
Site name	Select from the drop-down list the site that contains the data you want to import.
Query interval (mins)	Type the import interval in minutes. A value of zero(0) will issue a single query against the SQL Server source tables, which can potentially result in locking the tables. For any value greater than zero (0), the task will incrementally import the transactions in groups as determined by the query interval value. For example, if the query interval is 25 minutes (the default value) and the first transaction was logged at 1:30am, the transaction data import task imports all transactions logged between 1:30am and 1:55am, then it would import transactions between 1:55am and 2:20am. This pattern continues until the current time is reached, at which point the task stops. Note If the transaction database is in a different time zone than the Data Warehouse, the import will cease at the current time of the import, when converted into the time zone of the transaction database. For example, if the transaction database is in the EST time zone, and the Data Warehouse is in the PST time zone, and the current time is 8:00am in PST, the import will cease when the query interval reaches 8:00am in EST.
Incremental load	Select this option to import only new transaction data (based on a previous import).
Full load	Select this option to import all transaction data.

In the Retry Properties section, do the following:

Use this	To do this
Number of retry attempts	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.
Time between attempts (seconds)	Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.

Click OK.

Note
- You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.
On the Package menu, click Execute.

The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.
Click OK, and then click Done.

Transaction data is imported into the Data Warehouse.
Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

This data is now available for reports.

To import transaction data for all associated sites into your Data Warehouse

Run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse. For instructions, see Running the Configuration Synchronization DTS Task.
Expand Microsoft SQL Server, 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, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.
On the Task menu, click Transaction data import (Commerce Server).

In the Transaction Import Properties dialog box, on the General tab, do the following:

Use this	To do this
Description	Type a description for this import task.
Operation level	Select Data Warehouse level from the drop-down list.
Data Warehouse name	Select from the drop-down list the Data Warehouse associated with the sites for which you want to import transaction data.
Incremental load	Select this option to import only new transaction data (based on a previous import).
Full load	Select this option to import all transaction data.

In the Retry Properties section, do the following:

Use this	To do this
Number of retry attempts	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.
Time between attempts (seconds)	Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.

Click OK.

Note
- You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.
On the Package menu, click Execute.

The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.
Click OK, and then click Done.

Transaction data is imported into the Data Warehouse.
Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

This data is now available for reports.

Running the Transaction Data Import DTS Task

See Also

Additional resources