Scripting the Campaign Data Import DTS Task

Article
11/12/2009

The following script creates and runs the Campaign Data Import DTS task. This script can be used to create a package containing the DTS task to be run. The sample script can be run in two different ways. You can run the package on the command line by using the command DTSRun.exe. The file DTSRun.exe is automatically installed on your server when you install SQL Server. Or you can copy the script into a Visual Basic script file (.vbs) and run it by using cscript, for example, <drive>:cscript <filename>.vbs.

To successfully complete the import of data into the Data Warehouse, run the DTS tasks in the following order:

Configuration Synchronization
Web Server Log Import
Transaction Data Import
Product Catalog Import
Profile Data Import
Campaign Data Import
Data Deletion
Model Builder
IP Resolution
Report Preparation
Report caching

For more information about the DTSMarketingImport object, see DTSMarketingImport Object.

For a description of this DTS task, see Commerce Server DTS Tasks.

'**********************************************************************

' Campaign Data Import DTS Task

' This script creates a DTS package and runs it.

' Note: This DTS task cannot be run on a computer that only has the Commerce Server

' 2002 Business Analytics Stand-Alone feature installed. This DTS task gets the required

' connection strings for both the Commerce database and the Data Warehouse from the

' Admin object based on the site name set in the script.

'**********************************************************************
Dim oPackage 
Dim oTask 
Dim oStep 
Dim oProps 
Dim oTaskProps 
Dim iStatus
Dim i
Dim DTSStepExecResult_Failure

DTSStepExecResult_Failure  = 1

set oPackage = WScript.CreateOBject("DTS.Package")

'******************************************************************
' Define package properties.
'******************************************************************
oPackage.Name = "Campaign Data Import DTS Task "
oPackage.Description = "Imports data about ad and discount campaigns."

'******************************************************************
' Create a task.
'******************************************************************
'Make sure you are passing the correct ProgID.
Set oTask = oPackage.Tasks.New("Commerce.DTSMarketingImport")
oTask.Name = "Task1"
oTask.Description = "Creates a task for CampaignDataImport DTS"
Set oTaskProps = oTask.Properties

' Set Catalog DTS properties

' 0 for the site level import and 1 for the Data Warehouse level import
oTaskProps("SourceType").value = 1

' Make sure the SourceName is set to the correct Web site or Data
' Warehouse.
oTaskProps("SourceName").value = "Data Warehouse 1"

' Number of SQL Server retries in case of SQL Server failure
oTaskProps("NumRetries").value = 10
oTaskProps("RetryInterval").value = 20

oPackage.Tasks.Add oTask

'******************************************************************
' Create a step.
'******************************************************************
Set oStep = oPackage.Steps.New
oStep.Name = "Step1"
oStep.TaskName = "Task1"

' For custom tasks written in Visual Basic, the steps cannot run on a
' secondary thread.
oStep.ExecuteInMainThread = True

oPackage.Steps.Add oStep

'******************************************************************
' Execute the package.
'******************************************************************
oPackage.Execute
    For I = 1 To oPackage.Steps.Count
        If oPackage.Steps(I).ExecutionResult = DTSStepExecResult_Failure 
            Then
            iStatus = False
            MsgBox oPackage.Steps(I).Name + " in the " + _ 
            oPackage.Name + " failed."
        End If
    Next 

MsgBox oPackage.Name + " Done"
        
Set oStep = Nothing
Set oTaskProps = Nothing
Set oProps = Nothing
Set oTaskProps = Nothing
Set oPackage = Nothing<+]

Scripting the Campaign Data Import DTS Task

Additional resources