Microsoft KB Archive/919597

= How to troubleshoot a failed SMS DTS job in System Center Reporting Manager 2006 =

Article ID: 919597

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft System Center Reporting Manager 2006

-





SUMMARY
''After you configure a Microsoft Systems Management Server (SMS) 2003 Data Transformation Services (DTS) synchronization job in Microsoft System Center Reporting Manager (SCRM) 2006, that job may not import data into the SCRM database. If the SMS synchronization job fails and if you cannot determine the cause of this failure, perform troubleshooting steps in a linear manner. Verify whether the following DTS synchronization processes successfully finish:''


 * The scheduling process
 * The data extraction process
 * The file copy process
 * The extraction, transformation, and load (ETL) process

''To verify the success of these processes, view the log file entries that correspond to each process, and then examine the information in the SCRM-related tables on the computer that is running SMS 2003 Service Pack 2 (SP2) and on the SCRM server. Additionally, you can run Transact-SQL scripts to gather more troubleshooting information from the SMS database and from the SCRM database.''



INTRODUCTION
This article describes how to troubleshoot a failed SMS DTS job in System Center Reporting Manager 2006.



Overview of the troubleshooting process
If the SMS synchronization process is unsuccessful and if you cannot determine the location where the processing fails, perform troubleshooting steps in a linear manner. To do this, verify the data flow in the following order:
 * The scheduling process

Verify whether SMS has collected the schedule and whether SMS has entered the schedule into the SMS database.
 * The data extraction process

Verify whether data has been successfully extracted from the SMS database.
 * The file copy process

Verify whether the extracted data (.dat files) have been copied to the SCRM server.
 * The ETL process

Verify whether the ETL process has successfully completed.

How to troubleshoot the scheduling process
When you schedule an SMS DTS job in the SCRM Administrator Console, the following actions occur:  A schedule file is written to the outgoing folder for the particular SMS data source on the SCRMSMSShare share. The SCRMSMSShare share is known as the staging share. By default, the schedule file is written to the following folder:

C:\SCRM_Staging\ \Outbound

 The SCRM SMS Site Service on the computer that is running SMS 2003 collects the schedule file. The SCRM SMS Site Service creates a Microsoft SQL Server Agent job for the schedule file. The schedule is entered into the SMS database according to the schedule that you specified when you configured the SMS DTS job in the SCRM Administrator Console.

To verify whether the schedule file has been picked up by the SMS server, examine the %ProgramFiles%\Microsoft System Center Reporting Manager\Log\SCRMLog.log file on the computer that is running SMS 2003. The following entries appear in this file during a typical run of the Change Management Component thread of the SCRM SMS Site Service.

Note A typical run occurs when no schedule file or watermark file is present in the staging share.

CChangeManagement::FindAndCopyWatermarkFile : Watermark File not found at \\\SCRMSMSShare\<SMSDataSourceName>\Outbound\watermark.txt Watermark file not found in outbound folder cmd.exe /c &quot;dir /B /OD &quot;\\<SCRMServer>\SCRMSMSShare\<SMSDataSourceName>\Outbound schedule*.txt&quot; >> &quot;C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt&quot;&quot; successfully executed Opening temp file: C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt Successfully opened temp file: C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt Reading file name from temp file Failed to read string GetFileName Failed The system cannot find the file specified.

The Change Management Component thread runs every five minutes. However, SCRM only puts watermark files and schedule files on the staging share when it is required. Therefore, these entries appear in the SCRMLog.log file many times. When a schedule file is put on the staging share, the Change Management Component thread collects the file. In this situation, the following entries appear in the SCRMLog.log file: <pre class="fixed_text">Watermark file not found in outbound folder Message File Source is C:\Program Files\Microsoft System Center Reporting Manager\Extraction\Status\SCRM_Message.log Message File Dest is \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Inbound\SCRM_Message.log CMessageFileTransfer::OnPoll : Copy Message file is succeed cmd.exe /c &quot;dir /B /OD &quot;\\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\schedule*.txt&quot; >> &quot;C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt&quot;&quot; successfully executed Opening temp file: C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt Successfully opened temp file: C:\Program Files\Microsoft System Center Reporting Manager\Log\schtmp.txt Reading file name from temp file Preparing file name from read string Read from temp file Opening schedule file: \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt Successfully opened schedule file: \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt Reading schedule file: \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt Successfully Read schedule file: \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt Executing command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName = 'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta', @Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type = 0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor = 0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time = 141500,@active_end_time = 3200' Successfully Executed command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName = 'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta', @Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type = 0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor = 0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time = 141500,@active_end_time = 3200' CChangeManagement::FindAndCopyScheduleFile: Deleting schedule file \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt CChangeManagement::FindAndCopyScheduleFile: Successfully deleted schedule file \\<SCRMSERVER>\SCRMSMSShare\<SMSDataSourceName>\Outbound\Schedule1.txt Reading file name from temp file Failed to read string GetFileName Failed All the commands that appear in this log file run by using the credentials of the account that is used to start the SCRM SMS Site Service on the computer that is running SMS 2003. If error messages are logged when this account tries to connect to the SCRM server, follow these steps:
 * 1) Verify whether the path that is specified in the SCRMLog.log file is available from the computer that is running SMS 2003.
 * 2) Verify whether the account under which the SCRM SMS Site Service runs has been granted permissions to the path that is specified in the SCRMLog.log file.
 * 3) If no entries appear in the SCRMLog.log file, verify whether the SCRM SMS Site Service is running.

The following entry in the SCRMLog.log file shows that the SCRM_SP_CreateJob stored procedure has run on the computer that is running SMS 2003: <pre class="fixed_text">Executing command: EXEC SCRM_SP_CreateJob 'sms_cen',@JobName = 'SC_SMS_Delta-Dts1_from <SMSDataSourceName>' ,@enabled =1, @SyncName ='Delta', @Schedule ='@freq_type = 1,@freq_interval = 0,@freq_subday_type = 0,@freq_subday_interval = 0,@freq_relative_interval = 0,@freq_recurrence_factor = 0,@active_start_date = 20060420,@active_end_date = 0,@active_start_time = 141500,@active_end_time = 3200' When this stored procedure runs, a SQL Server Agent job is created for a full synchronization or for a delta synchronization, depending on the kind of synchronization that is specified in the schedule file. The SQL Server Agent job is configured to run on the same schedule as the schedule that is specified in the SCRM Administrator Console. When the SQL Server Agent job runs, a record that resembles the following is entered into the SCRM_MTD_SyncSchedule table of the SMS database.

If this record does not appear in the SCRM_MTD_SyncSchedule table of the SMS database when the SMS DTS job is scheduled, follow these steps:  Verify whether the SQL Server Agent job is enabled, and then make sure that the SQL Server Agent job is not failing. To do this, follow these steps:  Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.</li> Expand SQL Server Agent, expand Jobs, right-click the SQL Server Agent job, and then click View History. For example, right-click SC_SMS_Full-Dts1_from , and then click View History.</li> Examine the information that appears in the Log File Viewer -  dialog box to determine whether the SQL Server Agent job successfully finished.</li></ol> </li> If the SQL Server Agent job failed, make sure that the account under which the SQL Server Agent job runs is a member of the SC DW SMS DTS group on the SMS database server.</li></ol>

How to troubleshoot the data extraction process
After the record for the SMS synchronization job has been added to the SCRM_MTD_SyncSchedule table in the SMS database, it is detected by the SyncProcessAgentJob SQL Server Agent job. This SQL Server Agent job runs every five minutes. When the SyncProcessAgentJob runs, the data extraction process starts. When this process starts, the entry that appears in the SCRM_MTD_SyncSchedule table of the SMS database resembles the following.

If the value that appears in the Status column of this table is not set to InProgress within five minutes of the scheduled task being added to the SMS database, follow these steps:  Verify that the SyncProcessAgentJob SQL Server Agent job is enabled, and then make sure that this job is scheduled to run every five minutes. To do this, follow these steps:  Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.</li> Expand SQL Server Agent, expand Jobs, right-click SyncProcessAgentJob, and then click Properties.</li> In the Select a page pane, click General, and then make sure that the Enabled check box is selected.</li> In the Select a page pane, click Schedules, click the job that is displayed in the Schedule list box, and then click Edit.</li> In the Job Schedule Properties - Daily Schedule Every 5 Mins dialog box, examine the values that are displayed under Daily frequency.</li> Click Cancel, and then exit the Job Properties - SyncProcessAgentJob dialog box.</li></ol> </li> <li>Make sure that the account under which the SyncProcessAgentJob SQL Server Agent job runs is a member of the SC DW SMS DTS group on the SMS database server. To view the accounts under which the SyncProcessAgentJob SQL Server Agent job processes run, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Start the SQL Server Management Studio program, and then connect to the computer that hosts the SMS database.</li> <li>Expand SQL Server Agent, expand Jobs, right-click SyncProcessAgentJob, and then click View History.</li> <li>In the right pane of the Log File Viewer -  dialog box, expand the SyncProcessAgentJob job.</li> <li>Examine the information that appears in the Step Name column and in the corresponding Message column. Information that resembles the following is displayed. </li></ol> </li> <li>Examine the job history of the SyncProcessAgentJob SQL Server Agent job to determine whether the job is failing when it runs. To do this, repeat steps 2a through 2d.

If the SyncProcessAgentJob SQL Server Agent job fails on the Manage SyncProcess step, examine the ManageSynchProcess_Log.log file to help determine the reason for this failure. This log file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Log

</li> <li>Verify whether an earlier job that has a status of InProgress is displayed in the SCRM_MTD_SyncSchedule table of the SMS database. An earlier job must finish before a new job can start.</li></ol>

After the data extraction process starts, the progress is tracked in the following tables in the SMS database:
 * SCRM_MTD_ExtractQueue
 * SCRM_MTD_ExtractTargetStatus

The SCRM_MTD_ExtractQueue table displays the data extraction status for each SMS database table. To view the table names together with the data extraction status, run the following Transact-SQL query. SELECT SCRM_MTD_ExtractTargetList.TableName,SCRM_MTD_ExtractQueue.Status FROM SCRM_MTD_ExtractTargetList JOIN SCRM_MTD_ExtractQueue ON SCRM_MTD_ExtractTargetList.ExtractTargetListID = SCRM_MTD_ExtractQueue.ExtractTargetListID The SCRM_MTD_ExtractTargetStatus table displays the start time and the end time for each data extraction operation. Additionally, this table displays the output file name and the retry count for each data extraction operation. To view information about the synchronization job that is currently in progress, run the following Transact-SQL query. SELECT * FROM SCRM_MTD_ExtractTargetStatus WHERE SyncScheduleID IN (SELECT SyncScheduleID FROM SCRM_MTD_SyncSchedule WHERE status = 'InProgress') If the SCRM_MTD_ExtractQueue table displays an error for any extraction operation or if the SCRM_MTD_ExtractTargetStatus table displays a RetryAttempt value that is greater than 0 (zero) for any table, follow these steps: <ol> <li>Examine the DTS_MDS_SYNCH.log file to help determine the reason for the data extraction failure. This is the log file for the DTS job that performs the data extraction operation. This file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Log

</li> <li>Examine the DTSExtractPackage_Log.log file and the SCRMLog.log file to help determine the reason for the data extraction failure. These log files are located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Log

</li></ol>

To verify whether all the required data has been successfully extracted from the SMS database, examine the SCRM_Message.log file. This file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Extraction\Status

If all the required data has been successfully extracted from the SMS database, this file contains an &quot;Extraction Finished&quot; entry. Also, if the file copy operation to the SCRM server has not yet started, 91 .dat files should be present in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Extraction\DATA

If these 91 .dat files are not present in this folder, verify the value that appears in the ParamValue column of the ExtractionDataFilePath parameter entry of the SCRM_MTD_Configuration table in the SMS database.

How to troubleshoot the file copy process
After all the required data has been extracted from the SMS database and after the &quot;Extraction Finished&quot; entry has been written to the SCRM_Message.log file, the SCRM SMS Site Service copies the data files to the staging share on the SCRM server. The progress of this file copy operation is logged in the SCRMLog.log file. This file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Log

If no entries appear in this file, follow these steps: <ol> <li>Verify whether the SCRM SMS Site Service is started on the computer that is running SMS 2003.</li> <li>Verify whether the 91 .dat files are present in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Extraction\DATA

</li></ol>

If the SCRMLog.log file displays an entry such as &quot;Access Denied&quot; or &quot;Server not Found,&quot; follow these steps: <ol> <li>In the SMS database, examine the value that appears in the ParamValue column of the ShareDirectory parameter entry of the SCRM_MTD_Configuration table. This value displays the path of the particular SMS data source share on the SCRM server. This value must display the correct path of this SMS data source.</li> <li>Make sure that the account under which the SCRM SMS Site Service on the SMS server runs has Full Control permissions to the Inbound folder of the SCRM server's share that is specified in the ParamValue column of the ShareDirectory path. Also, make sure that this account has both of the following permissions assigned for the SCRM server's SCRMSMSShare share:

Change

Read

</li> <li>On the computer that is running SMS 2003, try to manually connect to the Inbound folder of the share that is specified in the ParamValue column of the ShareDirectory path. When you do this, first log on to the computer that is running SMS 2003 by using the credentials of the account under which the SCRM SMS Site Service runs.

Note By default, the SCRM SMS Site Service runs under the LOCAL SYSTEM account. In this situation, start a command prompt by using the LOCAL SYSTEM account, and then try to connect to the Inbound folder of the staging share on the SCRM server.</li></ol>

To verify whether the file transfer operation successfully finished, follow these steps: <ol> <li>Verify that the &quot;File Transfer Finished&quot; entry appears in the SCRMLog.log file. This file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Log

</li> <li>Verify that the &quot;File Transfer Finished&quot; entry appears in the SCRM_Message.log file. This file is located in the following folder on the computer that is running SMS 2003:

%ProgramFiles%\Microsoft System Center Reporting Manager\Extraction\Status

</li></ol>

How to troubleshoot the ETL process
After all the SMS data files have been successfully copied to the SCRM server, the ProcessDWJob SQL Server Agent job on the SCRM server starts the ETL process. This SQL Server Agent job starts the various DTS jobs that execute the ETL stored procedures and operations. You can troubleshoot the ETL process by using the following tables and views in the SystemCenterStaging database on the SCRM server:
 * STG_MTD_Status_Extract

This table logs the progress and the details of the data extraction operation from the .dat files to the SystemCenterStaging database.
 * STG_MTD_AgentStatus

This table logs the start times and the end times of each stage of the ETL process.
 * STG_ETL_STATUS_VIEW

This view displays the start time, the end time, the status, and the row count for each task in the ETL process.
 * STG_ETL_HISTORY_VIEW

This view displays the start time, the end time, the status, and the row count for each task in the ETL process for SMS Synchronization jobs that have finished.

To determine the status of the ETL process, examine the latest entries in the STG_MTD_AgentStatus table. The following four high-level steps are logged in this table:
 * Extract

This step covers the extraction of data files to the Inbound tables.
 * Transform

This step covers the transformation of Inbound data to Outbound tables.
 * Load

This step covers the loading of data in the data warehouse.
 * Watermark

This step covers watermark generation and the removal of data files.

When each of these steps starts, the current date and time is recorded in the ExecutionStart column of the STG_MTD_AgentStatus table. When the particular step ends, the current date and time is recorded in the ExecutionEnd column of this table. Therefore, all the following actions occur:
 * A step that has completed has both an ExecutionStart value and an ExecutionEnd value.
 * A step that has not yet started does not have an ExecutionStart value or an ExecutionEnd value.
 * A step that has started but that has not yet completed has an ExecutionStart value but does not have an ExecutionEnd value.

You can use this information to help determine where the ETL process fails.

The Extract step
If the ETL Extract step does not finish, follow these steps: <ol> <li>Examine the STG_MTD_Status_Extract table in the SystemCenterStaging database. The following columns in this table indicate the .dat files that have been successfully extracted to the SystemCenterStaging database and the files that are currently being extracted: <ul> <li>DataFileName</li> <li>Status</li> <li>TaskStartDateTime</li> <li>TaskEndDateTime</li></ul>

This table also contains historical data for earlier SMS synchronization jobs. You can use this historical data to help determine how long it may take to extract each file.</li> <li>If the extraction process failed for one or more .dat files, examine the LOG_DTS_EXTRACT.Log file to determine whether one or more failure messages are logged. This file is located in the following folder on the SCRM server:

%ProgramFiles%\Microsoft System Center Reporting Manager\DTS\SMS

</li></ol>

The Transform and Load steps
During the Transform step and the Load step of the ETL process, SMS data is moved from the Inbound staging tables in the SystemCenterStaging database to the Outbound staging tables in the SystemCenterStaging database. This data is then inserted into the SystemCenterPresentation database. You can use the STG_ETL_STATUS_VIEW view to examine the status and the details of these steps. This view lists each task that is performed by these steps, together with the task's start time, end time, and status.

The ETLTaskStatusDescription column of the STG_ETL_STATUS_VIEW view displays one of the following values to indicate the status of each task:
 * COMPLETED
 * RUNNING
 * WAITING

If a task fails, the ProcessDWJob SQL Server Agent job fails. The ProcessDWJob SQL Server Agent job runs again within five minutes. The ProcessDWJob SQL Server Agent job then runs the failed task.

Note The ETLTaskStatusDescription column does not display a failure status for the failed task. In this situation, the task status remains with a value of RUNNING. A new row is created for the task when the ETL process next starts.

Therefore, if the STG_ETL_STATUS_VIEW view displays multiple entries for the same task and if each of these entries has a ETLTaskStatusDescription value of RUNNING, the task is failing.

If you determine that a task is failing, examine the ETLTaskPackageName column for the failing task. This column displays the DTS package that starts the task. From this information, you can determine which DTS log file to examine to help determine the reason that the task is failing. The following table lists the DTS packages together with their corresponding log files. All these log files are located in the %ProgramFiles%\Microsoft System Center Reporting Manager\DTS\SMS folder on the SCRM server.

After you determine the DTS task that is failing, examine the corresponding log file for error messages that may indicate the reason for the DTS task failure.

Data collection scripts
In addition to the log file information, you can run data collection scripts to obtain troubleshooting information from the SMS database and from the SCRM database. You can use the information that you gather to help troubleshoot the reason for the failing SMS synchronization job.

On the SMS database server, run the following query against the SMS database to collect troubleshooting data. /* Purpose: purpose of this script is to collect data to help troubleshoot SMS data source issue.
 * This script has to be run on the SMS Database

print '** START: SMS Data Collection for Troubleshooting SCRM Data Source **' print '-- Following is output from Staging Meta data tables --' print '-'

print '** Output for the SCRM SMS Data Source Configuration Parameters **' print '--' SELECT * FROM SCRM_MTD_Configuration

print '** Output for the Table List to be BCP OUT in the current running synch print '--'   SELECT * FROM SCRM_MTD_ExtractQueue

print '** Output the List of Tables and their Synch Types (Full, Delta) Applicability **' print '-'

SELECT * FROM SCRM_MTD_ExtractTargetListSyncType

print '** Outputs information on Table/DAT File specific BCP OUT details **' print '' SELECT * FROM SCRM_MTD_ExtractTargetStatus

print '** Outputs current state of WaterMarks on the SMS Database **' print '-' SELECT * FROM SCRM_MTD_LowHighTideWaterMark

print '** Outputs current state of Schedule Queue for the Data Source**' print '' SELECT *FROM SCRM_MTD_SyncSchedule On the SCRM database server, run the following query against the SCRM database to collect troubleshooting data. /* Purpose: purpose of this script is to collect data to help troubleshoot SCRM issue.
 * This script has to be run on the SCRM SQL Server



USE SystemCenterStaging GO

print '** START: SCRM Data Collection for Troubleshooting **' print '-- Following is output from Staging Meta data tables --' print '---'

print '** Output for the High Level Job Steps (E,T,L,Watermark) **' print '---' SELECT * FROM STG_MTD_AgentStatus

print '** Output for Staging Configuration Parameters **' print '-' SELECT * FROM STG_MTD_Configuration

print '** Output for the Status of the Bulk Insert of Files into Inbound **' print '' SELECT * FROM STG_MTD_Status_Extract

print '** Outputs the Current Staging Lineage Number **' print '' SELECT * FROM STG_LineageStaging_DW

print '-' SELECT * FROM STG_ETL_ErrorStringLog_VIEW

print '-' SELECT * FROM STG_ETL_ErrorStringState_VIEW

print '** Outputs the DTS Run History for Data Sources **' print '--' SELECT * FROM STG_ETL_HISTORY_VIEW

print '** Outputs the Current DTS Running Status for Data Sources **' print '-' SELECT * FROM STG_ETL_STATUS_VIEW GO

print '** Outputs the Job History for SCRM DWProcessJob **' print '---' SELECT j.name,h.* FROM msdb..sysjobs j JOIN msdb..sysjobhistory h ON j.job_id = h.job_id WHERE j.name = 'ProcessDWJob'

print '** Outputs the list of SMS Data Sources added **' print '' SELECT DataSourceName FROM SystemCenterConfiguration.dbo.SC_MMC_DataSource WHERE DataSourceTypeID = 1 GO

print '** Outputs the records in the Lineage Dimension from Presentation DB **' print '---' SELECT * FROM SystemCenterPresentation.dbo.SC_Lineage_Dimension

print '--- END   --' print '-'

Keywords: kbhowto kbinfo KB919597

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.