How to execute SSRS report from Sql Server job agent

 

Sometimes, there are needs to execute SSRS directly from a sql server job or inside a SSIS package.

If it is from a SSIS package, you need to use script component and use the web service exposed by SSRS

If you need to execute it through a Sql server job agent, below is the step

 

1.  Create a one time subscription for that report.

2. Find out the Subscription ID for that report

SELECT s.SubscriptionID

FROM dbo.Catalog AS c

INNER JOIN dbo.ReportSchedule AS s

             ON   c.ItemID = s.ReportID

WHERE

      c.Name = ‘your report name’

3. Execute dbo.AddEvent proc on ReportServer

 

EXEC ReportServer.dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SubscriptionID

 

This SSRS rpt will be executed  by this job.