So you’ve spent weeks or months building a data platform and pipeline and then the finance team wants to manually refresh data because its month or year end. If your refresh jobs live in SQL Server Agent, then you’re in a pickle because you don’t want to grant them permissions on a production SQL instance or have them install SQL Management Studio. In this case SSRS can come to the rescue!
Create a simple report that has a data connection to the MSDB database that has a combo box of SQL agent jobs using something like
select name from msdb.dbo.sysjobs where enabled=1 and name like ‘%only jobs with a specific prefix%'
then create a dataset that uses the parameter from this and fire off some SQL like
EXECUTE ('dbo.sp_start_job @job_name=''' + @jobName + '''')
and hook this data set up to a table on the report so that this statement executes.
Make this more user friendly by adding more SQL to this to delay for ~ 10 seconds to wait for the Agent job to fire up, and the return the jobs that are currently running by querying the sysJobActivity table (hit Google for tons of examples on querying the MSDB to see what is running).
Naturally you would put this report in a special folder that only administrators and super users have access to.
This post was previously posted to LinkedIn.