Using a SSRS Report to run SQL Agent Jobs

Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel.  Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner.

With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did:

Step One

Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.

Step Two

Create 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s