SSRS Report to run SQL Agent Jobs

Data Warehouse latency is often a complaint ,generally heard, from end users when trying to access data via either Reporting Services reports or Excel. 
To create a report that could kick off the SQL Agent job that processed Data Warehouse load and Cube update.  It is a pretty simple report to create.  Here are the steps :
Step OneCreate a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.
Step TwoCreate a DataSet that runs the system stored procedure sp_start_job with the name of the job.






Step Three
Add some text!  Let the user know what’s going on after they click on the report otherwise it will just show a blank report.  Drag a textbox over and add the appropriate text.


Deploy the report and test!
There are some circumstances where you would not want to use this method:

·         Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.

·         The job takes longer than just a few minutes to process.  Remember you are trying to improve latency.  You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)

·         You haven’t trained your end users in what the report does.  You don’t want end users clicking this report over and over again because it is running a major process