Example SQL Query: Status Query for DTS Jobs
You can run the SQL status query in this example against the SystemCenterStaging database to view more detailed DTS job status for the following states:
**Inbound Loading **When the SQL query and the DTS job are in this state, the number of records that have been loaded, out of the total number of records to load in the Inbound table of the SystemCenterStaging database, is displayed.
Transformation In Progress When the SQL query and the DTS job are in this state, the number of records that have been transformed, out of the total number of records to transform, is displayed.
Loading In Progress When the SQL query and the DTS job are in this state, the number of records that have been loaded in the SystemCenterPresentation database, out of the total number of records to load in the SystemCenterPresentation database, is displayed.
Make sure to replace <DataSourceName> in this code example with the SMS data source name as it appears in the SCRM Administrator console.
For additional information about running SQL queries, see How to Run SQL Queries for Detailed SMS DTS Job Status.
Status Query Example
Use SystemCenterStaging
declare @state varchar(200), @AgentStatusId Int, @dsn as varchar(20),
@detailStatus as varchar(200)
--DataSource name (variable below) is to be set proper data source
-- name for which the ETL status is to be known
select @dsn = '<DataSourceName>'
SELECT @AgentStatusId = AgentStatusId, @state =
CASE AgentStepName
WHEN 'Extract' THEN
CASE AgentStepStateID
WHEN '2' THEN 'Inbound Loading In Progress'
WHEN '3' THEN 'Inbound Loading Finished'
WHEN '4' THEN 'Inbound Loading Failed'
ELSE NULL
END
WHEN 'Transform' THEN
CASE AgentStepStateID
WHEN '2' THEN 'Transformation In Progress'
WHEN '3' THEN 'Transformation Finished'
WHEN '4' THEN 'Transformation Failed'
ELSE NULL
END
WHEN 'Load' THEN
CASE AgentStepStateID
WHEN '2' THEN 'Loading In Progress'
WHEN '3' THEN 'Loading Finished'
WHEN '4' THEN 'Loading Failed'
ELSE NULL
END
WHEN 'Watermark' THEN
CASE AgentStepStateID
WHEN '2' THEN 'Watermark file generation In Progress'
WHEN '3' THEN 'ETL Cycle Success'
WHEN '4' THEN 'Watermark Updation Failed'
ELSE NULL
END
ELSE NULL END
FROM
STG_MTD_AGENTSTATUS
WHERE DataSourceName = @DSN
AND AgentStatusID = (SELECT MAX(AgentStatusID) FROM
STG_MTD_AGENTSTATUS WHERE DataSourceName = @DSN AND
AgentStepStateID <> 1)
----------------------------------------------------------------
select @detailStatus = cast(EStatus as varchar(200)) + ' of ' + cast(Alls as varchar(200)) + ' are loaded to Inbound'
from
(select count(*) as Alls from STG_MTD_Status_Extract AllsTable
inner join STG_MTD_AGENTSTATUS Agent on
AllsTable.ExtractionId = Agent.SiteExtractionId and
agent.datasourceName = @DSN and agent.datasourcename = AllsTable.DataSourceName
) as AllCounts,
(select count(*) as EStatus from STG_MTD_Status_Extract
EStatusTable
inner join STG_MTD_AGENTSTATUS Agent on
EStatusTable.ExtractionId = Agent.SiteExtractionId and
agent.datasourceName = @DSN and agent.datasourcename =
EStatusTable.DataSourceName
where Status = 'Inbound Loaded'
) as ExtractedCount
where
(@state = 'Inbound Loading In Progress') or
(@state = 'Inbound Loading Failed')
select @detailStatus = cast(TransCompleted as varchar(200)) + '
of ' + cast(Alls as varchar(200)) + ' are transformed to outbound'
from
(select count(*) as Alls from
(select distinct etlTaskDescription from STG_ETL_STATUS_VIEW
where datasourcename = @dsn and ETLTaskDescription
like 'Transform%'
) IntAlls
) AllCounts,
(select count(*) as TransCompleted from
(select distinct etlTaskDescription from STG_ETL_STATUS_VIEW
where datasourcename = @dsn and ETLTaskStatusDescription =
'Completed' and ETLTaskDescription like 'Transform%'
) TransCompletes
) TransCount
where
@state = 'Transformation In Progress' or @state =
'Transformation Failed'
select @detailStatus = cast(TransCompleted as varchar(200)) +
' of ' + cast(Alls as varchar(200)) + ' are Loaded to Presentation'
from
(select count(*) as Alls from
(select distinct etlTaskDescription from STG_ETL_STATUS_VIEW
where datasourcename = @dsn and ETLTaskDescription
like 'Load%'
) Alls
) AllCounts,
(select count(*) as TransCompleted from
(select distinct etlTaskDescription from STG_ETL_STATUS_VIEW
where datasourcename = @dsn and ETLTaskStatusDescription
= 'Completed' and ETLTaskDescription like 'Load%'
) LoadCompletes
) LoadCount
where
@state = 'Loading In Progress' or @state = 'Loading Failed'
select case when @detailStatus is NULL then
'There is no pending synchronization waiting for completion'
else @detailStatus end as status