Commerce Server Blog

After adding data warehouse resource to commerce server site, we must create the DTS tasks that extract the data, transform and load into data warehouse. Using data warehouse import wizard we can accomplish this. To define a DTS package follow below steps.

1. Click on Start => All Programs => Microsoft commerce server 2009 =>  Tools => Data warehouse import wizard. A dialog box appears. Click on next button.
2. Select commerce server site from the dropdown and check the type of imports you would like have and click on Next button.
   
   Web server logs => It consists of data logged from IIS (c:\windows\system32\logFiles). You can find IIS logs in world wide web consortium (W3C) extended log format. This data includes duration of the visit, referring sites, clicks etc.
   Resolve IP address => IP addresses are converted to domain names.
   Product catalog => Includes dimensional data like product name, size, color, etc.
   Transactions => It includes purchase orders and requisition numbers and useful in determining how well products are selling.
   Profiles => It includes user information such as user name, visits, etc
   Campaigns => includes marketing campaign information.
   Note: If you select commerce server site which doesn’t have data warehouse resource installed then you may get this error.
   ”Failed to connect to the datawarehouse for this site.  You must unpup the datawarehouse resource before running the import wizard for this site or choose a different site.”
3. On mode of import screen, select whether you want to import full data or import data incrementally. After selection, click Next button.
4. On log file location screen, you must specify the location of the web server logs(c:\windows\system32\logFiles). Click next button.
5. On File selection screen, select the log files that you want to import. Click next button.
   
6. On Import executive options screen. You have option save the configuration or run the import process immediately.  Click Next & Finish button to continue.
7. If you have selected “Run the import immediately” option in last step, you can see DTS package execution dialog window.
   
   Note: if you didn’t select log files in step no. 5, you may get error while performing web server log import. To overcome this error, you have to unselect “web server logs” option in step no. 2

Once installed you can able to see DTS package in SQL Server. Follow below steps to see

1. Click start  => All Programs => Microsoft SQL Server 2005 => SQL Server management Studio
2. In SQL server management studio, expand management => Legacy => Data Transformation Services.
3. Right click the package you wish to run and click open. You may get error “SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.” if you don’t have SQL Server 2000 DTS Designer components installed.
4. Click the execute button to execute the DTS package.

If you like this post, please click on our sponsor advertisement.