Share via


ETL Process for the Configuration Synchronization DTS Task

The Configuration synchronization DTS task synchronizes information between the Administration database and the Data Warehouse. Specifically, the task synchronizes the Data Warehouse with your Commerce Server application after you change configuration settings, such as site topology, query strings, crawlers, and report definitions. The Configuration synchronization DTS task should be run before any of the other import or delete tasks are run, especially if you change configuration settings.

The following tables list the columns extracted from the source tables in the Administration database, the transformation performed, and the tables that the data is saved to in the Data Warehouse.

Synchronizing the Site Topology

Source property name from the ResourceProps table of the Administration database

Transformation

Target columns in the Site table of the Data Warehouse

WhenImportIsComplete

Generated

Value of s_Value plus 1 is loaded.

EndImportAction

N/A

Generated

Set to zero (0).

IsAdjustTimeStamps

ExcludeCrawlers

Copy Column

IsExcludeSpiders

i_QSSaveWithUri

Generated

Value of s_Value plus 1 is loaded.

IsSeparateUriAndQueryString

UseCookiesForInferences

Copy Cell

IsUseCookiesForInferences

MultipleUsersUseTheSameName

Copy Cell

IsUseUsernameForInferences

UseUPMCookiesForInferences

Copy Cell

IsUseUPMCookiesForInferences

s_InfNames

Copy Cell

CookieIdentifiers

IfOverlapDetected

Generated

Value of s_Value plus 1 is loaded.

OverlapAction

MinutesRecordsOverlap

Copy Cell

OverlapMins

s__Name from Sites Table in MSCS_Admin database

Copy Cell

SiteName

TimeZone

Copy Cell

TimeZone

IdleTmeBeforeVisitEnds

Copy Cell

VisitTimeOut

RetryCount

Copy Cell

RetryCount

RetryExhaustAction

Copy Cell

RetryExhaustAction

g_nStartDayOfWeek

Copy Cell

Default value is zero (0).

WeekStartDay

Source PropertyName from the ResourceProps table of the Administration database

Transformation

Target columns from the ServerGroup table in the Data Warehouse

s_WebServerName

Copy Cell

varList

s_LogFileDirectory

Copy Cell

LogFileDir

s_LogType

Copy Cell

LogFormatID

s_WebServerMachine and s_WebServerName

Concatenation

"LDS For" s_WebServerMachine/ s_WebServerName.

Used only if varList is empty.

ServerGroupName

N/A

Generated

Value of 25 (GMT) is loaded.

TimeZone

s_LogFileCodePage

Copy Cell

LogFileCodePage

s_URLEncodingCodePage

Copy Cell

URLEncodingCodePage

Source PropertyName from the ResourceProps table of the Administration database

Transformation

Target columns from the VirtualServer table in the Data Warehouse

s_DirectoryIndexFiles

Copy Cell

DirIndexes

LocalDomainDnsServer

Copy Cell

If cell is empty load “Unknown”

LocalDomain

s_WebServerMachine and s_WebServerName

Generated

s_WebServerName on s_WebServerMachine

VserverName

N/A

Generated

Value of 1 is loaded.

VserverType

Source PropertyName from the ResourceProps table of the Administration database

Transformation

Target columns from the ServerBinding table in the Data Warehouse

s_ServerBindings

Middle of String

HostHeaderName

s_ServerBindings

Middle of String

IPAddress

N/A

Generated

If the binding is secure, 443 is loaded. If it is not secure, 80 is loaded.

IPPort

Source PropertyName from the ResourceProps table of the Administration database

Transformation

Target columns from the SiteURL table in the Data Warehouse

s_ExFileExpressions and s_ExFileTypes

Concatenation

Asterisk (*) separated list of expressions and types.

ExcludeCriteria

s_ExHosts

Copy Cell

ExcludeHosts

TruncateTopDir

Copy Cell

IsChopTopLevelDir

s_NonSecureHostname and s_VirtualRootName

Concatenation

http://" & s_NonSecureHostname & "/" & s_VirtualRootName

URL

s_NonSecureHostname and s_VirtualRootName

Concatenation

http://" & s_NonSecureHostname & "/" & s_VirtualRootName

URLExt

Query String Updates

Source Property Name from the ResourceProps table of the Administration database

Transformation

Target columns from the URIQuery table in the Data Warehouse

s_QSName

Copy Cell

QueryString

N/A

Generated

QueryStringKey

Source Property Name from the ResourceProps Table of the Administration Database

Transformation

Target Columns from the SVQStringName Table in the Data Warehouse

s_QSName

Copy Cell

QStringName

Source Property Name from the ResourceProps table of the Administration database

Transformation

Target columns from the LinkSVQStringName table in the Data Warehouse

s_QSName

Copy Cell

Load a LinkSVQStringName for every SiteURLID listed in s_OSApplicationList.

LinkSVQStringName

Source Property Name from the ResourceProps table of the Administration database

Transformation

Target columns from the MVQStringName table in the Data Warehouse

s_QSName

Copy Cell

QStringName

Crawler Updates

Source Property Name from the ResourceProps table of the Administration database

Transformation

Target columns from the Crawler table in the Data Warehouse

Crawler.ini

Read File

CrawlerString

i_SiteID from Sites Table in MSCS_Admin database

Copy Cell

SiteID

See Also

Other Resources

ETL Processing for DTS Tasks