ETL Process for the Configuration Synchronization DTS Task

  • Article

The Configuration synchronization DTS task synchronizes information between the Administration database and the Data Warehouse. Specifically, the task synchronizes updates made to the 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.

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 insecure, 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

Running the Configuration Synchronization DTS Task

Scripting the Configuration Synchronization DTS Task

Workflow for Running DTS Tasks

Extending the Data Warehouse

Data Warehouse Schema

Copyright © 2005 Microsoft Corporation.
All rights reserved.