SQL scripts to check the MR integration
Support uses two scripts to help identify the current state of an integration and to review the integration history as well. These are useful in identifying how long the initial integration takes, knowing when the initial integration has completed, and reviewing the integration history to identify if any issues have occurred.
The first script shows the current status of the integration. On the initial run the LastRunTime and NextRunTime columns will show null until those tasks have completed once. Afterwards these columns will show the times of the last run and next scheduled run. The Progress column shows the percent of completion of the current task load. A StateType of 5 means that the integration task has completed successfully. A 3 means it is processing and a 7 means there is an error.
Notice:
Microsoft provides programming examples for illustration only, without warranty expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This mail message assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.
select t.name, ts.StateType, ts.Progress,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), ts.LastRunTime)
as LastRunTime,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), ts.NextRunTime)
as NextRunTime,
tr.IsEnabled, tr.Interval, tr.UnitOfMeasure
from scheduling.Task t join scheduling.TaskState ts
on t.id = ts.TaskId join Scheduling.[trigger] tr
on t.TriggerId = tr.id where TypeId in
(’55D3F71A-2618-4EAE-9AA6-D48767B974D8′, ‘D81C1197-D486-4FB7-AF8C-078C110893A0’)
The second script shows the results of each task run. Here we can see how long each task has taken to run and how many records were modified. To verify the initial load has completed look for an entry from the Fact task which loads the transactions into the data mart. There should also be an entry from the maintenance task with roughly the same number of records that were reported in the Fact task. Once the Fact task and Maintenance task have run, the integration is complete and you can generate reports in MR.
These logs are truncated after several days so if the integration has been in operation for over a week you won’t see the results from the initial load any longer.
select CIG.[Description], ST.[Name], SM.[Text],
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[StartTime])
as LocalStartTime,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[EndTime])
as LocalEndTime,
SL.[TotalRetryNumber], SL.[IsFailed], STT.[Name] as TaskType
from [Scheduling].[Log] SL with (nolock)
inner
join [Scheduling].[Task] ST with (nolock) on SL.TaskId = ST.Id
inner
join [Scheduling].[Message] SM with (nolock) on SL.Id = SM.LogId
inner
join [Scheduling].[TaskType] STT with (nolock) on ST.TypeId = STT.Id
inner
join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = ST.[CategoryId]
order
by SL.[StartTime] desc
