As a SSRS developer, you may need to trigger some reports after the data load was success. By default, microsoft report automation, will allow you to set the time of report execution. It wont care about your data load and success/failure of the data load.
Triggering report before the completion of data load or data load was failed, result the incorrect report.
To handle this scenario, follow the steps:
1. Schedule the report to execute once in the history date.So it wont run by the schedule in the future.
2. After the data load operation, invoke the below SQL statement with the report name. It will use the details that you specified in the subscription and send the report by mail or by any mean.
insert into THUBISPSDB.ReportServer.dbo.[Event]
([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])
select NewID(),EventType,[EventData], GETUTCDATE(), NULL, NULL
from THUBISPSDB.ReportServer.dbo.Schedule a
inner join THUBISPSDB.ReportServer.dbo.ReportSchedule b on a.ScheduleID=b.ScheduleID
inner join THUBISPSDB.ReportServer.dbo.Catalog c on b.ReportID=c.ItemID
where c.Name='SUMMARY.rdl'
Triggering report before the completion of data load or data load was failed, result the incorrect report.
To handle this scenario, follow the steps:
1. Schedule the report to execute once in the history date.So it wont run by the schedule in the future.
2. After the data load operation, invoke the below SQL statement with the report name. It will use the details that you specified in the subscription and send the report by mail or by any mean.
insert into THUBISPSDB.ReportServer.dbo.[Event]
([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])
select NewID(),EventType,[EventData], GETUTCDATE(), NULL, NULL
from THUBISPSDB.ReportServer.dbo.Schedule a
inner join THUBISPSDB.ReportServer.dbo.ReportSchedule b on a.ScheduleID=b.ScheduleID
inner join THUBISPSDB.ReportServer.dbo.Catalog c on b.ReportID=c.ItemID
where c.Name='SUMMARY.rdl'
