Sql-server – How to determine if an SSRS data driven subscription job has completed

sql serverssrs

I have a report that generates PDF's using a data driven subscription process.

When the job is started, it shows a success status shortly afterwards, however I believe that this is really asynchronous and therefore cannot be used as an indication of the jobs status. Is there a reliable indicator that can be used?

Ideally this would be something that would allow me to automate a check in a stored procedure.

Best Answer

Using the ReportServer database, I have a script that appears to work.

Querying the same table as my data driven query

select count(distinct param)
from MyDataDrivenSourceTable

and compare the result of that to the below

    SELECT count(*)
  FROM [ReportServer].[dbo].[Schedule]
  INNER JOIN [ReportServer].[dbo].ReportSchedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
  INNER JOIN [ReportServer].[dbo].[ExecutionLogStorage] ON ExecutionLogStorage.ReportID = ReportSchedule.ReportID
  WHERE Schedule.ScheduleID = '********INSERT JOB NAME GUID HERE********'
  AND TimeStart >= schedule.LastRunTime

Then I should be able to compare the results to know when it has completed.

I'm relying on an internal table to SSRS which could change at any time, so I appreciate this method is totally unreliable, but it seems better than polling to see which files have appeared on disk.

This makes some massive assumptions:

  • The source table is not updated in-between the start of the SSRS subscription and when the check is performed.
  • The last run time is updated at the start of the execution.
  • I have no idea what would happen if the job was called again half way through the run.
Related Question