About SMS DTS Jobs
After SCRM 2006 is set up, you add an SMS data source by using the SCRM Administrator console. This process installs the SMS data source component on the SMS site server as detailed in the SCRM Getting Started Guide. After an SMS data source is added or recovered, you should run a full data synchronization. Then you should run delta synchronizations daily.
Two DTS jobs are created in the SCRM Administrator console when an SMS data source is added:
Delta All tables are synchronized, but on tables that support watermarks, only the data that has been updated or changed since the watermark is imported. Watermarking is used to support delta synchronization of SMS data sources. Source tables that do not support watermarks are imported in their entirety.
Full Full synchronization imports all data from all SMS source tables; this may include data that has already been imported. When a full synchronization runs, watermarks are reset.
You must open the job properties and enable and schedule the DTS jobs before the jobs can run.
Note
The Account tab of the SMS DTS Job property page is not used.
SMS DTS Job Processes
The following steps outline the SMS data transfer process.
Scheduling
During this process, you schedule the SMS DTS job in the SCRM Administrator console. Schedule<Number>.txt is created in the Outbound directory for the SMS data source in the SCRM Staging share on the SCRM server. On the SMS server, the Change Management Component thread of the SCRM SMS Site Service (SCRMClientSvc.exe) checks the Outbound directory on the SCRM server for schedule files. The service then runs a stored procedure that creates a SQL Agent Job and schedules it to run on the schedule that was configured in the SCRM Administrator console. When the agent job runs, SCRM records the current date and time. This process is tracked in SCRMLog.log.
Data extraction from the SMS database
The SyncProcessAgentJob SQL Agent Job on the SMS server runs every five minutes (300 seconds), by default, and facilitates the process of using BCP.exe to extract data from the SMS database during full and delta sync jobs for SCRM. The SyncProcessAgentJob job performs the following steps:
Gets the sync schedule from the database and starts the sync process if necessary. Status information is written to ManageSynchProcess_Log.log.
Starts that DTS job that uses BCP.exe to extract data from the SMS site database. Status information is written to DTS_MDS_SYNCH.log, DTSExtractPackage_Log.log, and SCRM_Message.log. This data is initially extracted to the Data subfolder on the SMS server. The default location for the extracted data is <InstallationDirectory>\Extraction\DATA.
Completes the extraction process and writes the status file on the distribution share on SCRM server. Status information is written to DTS_EXTRACT_COMPLETION.log and DTSExtractCompletePackage_Log.log.
File transfer to the SCRM server
When data extraction is complete, DAT files, SCRM_ExtractStatus.log, and SCRM_Message.log are copied to the SCRM server. Completion of data extraction is marked by an
Extraction Completed
message written to SCRM_Message.log. The file copy is done by the File Extraction and Transfer Component thread of the SCRM SMS Site Service. This process is logged in the SCRM_Log.log file.
Extract, transform, and load (ETL) process on the SCRM server
SMS data is extracted from DAT files that were created by the BCP utility into the SystemCenterStaging database. Any necessary transformations are made. Data is loaded into the SystemCenterPresentation database where it is used for reporting.
Watermark process
When the ETL process is complete, an empty Watermark.txt file is placed in the Outbound directory for the SMS data source. This file is retrieved by the SMS site server and the watermark update is started. The SMS site server then marks the job as complete.