• Enabling Business Users to Refresh CubesData Warehouse or Run ETL Jobs via SSRS

Enabling Business Users to Refresh Cubes/Data Warehouse or Run ETL Jobs via SSRS

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.

2018-04-11T13:54:37+00:00 Tags: , , |

About the Author:

Stefan Quinn
Stefan specializes in a number of areas of expertise, primarily; the Microsoft BI & Analytics stack, Azure data warehouse and databases, Power BI, SQL Server stack : Reporting Services (SSRS), Analysis Services (SSAS - tabular and multi dimensional), and Integration Services (SSIS).

Leave A Comment