Sifiso W. Ndlovu

How to use SQL Server Reporting Services (SSRS) to execute SQL Agent Jobs

August 22, 2016 by

Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilized to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organization that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.

The Notion of Self-Service ETL Execution

At the outset, leaving it to business users to run ETL solutions should not be the preferred way of doings. Instead, business users should only be given control of manipulating and reporting on the data. This is where the notion of self-service reporting becomes useful as power users from the business can connect to a data model and slice and dice according to their requirement. Nevertheless, there could be viable circumstances that force data teams to extend business users control of reporting to executing ETL solutions.

Whatever your reasons for allowing business to execute ETL solutions, you should try to keep it simple for business users to do so. In our case, we devised a way for self-service ETL execution using SQL Server Reporting Services (SSRS) as most business users are already familiar with the tool.

In the following sections, we will take a look at three steps required to setup self-service ETL execution using SSRS as a business interface.

Step 1: Setup a SQL Agent Job

The first step involves setting up a SQL Server agent job that will execute an ETL solution. You can skip this step if you already have a SQL Server Agent job that executes some form of an ETL solution. For the purposes of this discussion, I set up a sample SQL Server agent job, referred to as My Dummy Job. The properties of this sample job are shown in Figure 1.

Figure 1: Properties of My Dummy Job

The complete T-SQL script for My Dummy Job can be found in the Downloads section at the bottom of this article.

Step 2: Configuring SSRS Dataset

Now that we have configured out SQL Server agent job we switch our focus to SSRS development and deployment. In this step, we set up a sample stored procedure – RunMyJob – that will be embedded in SSRS datasets. The definition of this sample stored procedure is shown in Script 1.

Script 1: Definition of RunMyDummyJob Stored Procedure

The stored procedure accepts a string parameter that has two possible values:

  1. Refresh – retrieves My Dummy Job latest execution history
  2. Load – executes My Dummy Job

As it can be seen, the stored procedure is divided into three parts. Part A declares and uses local variable @ConditionCheck variable to determine whether or not My Dummy Job is currently being executed. This is done to prevent executing a job that is already executing. Part B is used to execute My Dummy Job provided the @ConditionCheck variable value is 0 and the @RunOption variable value is Load. The last part of the stored procedure is executed regardless of the value of the @ConditionCheck variable. This last part retrieves latest activities relating to My Dummy Job.

Step 3: Report Development and Deployment

Now that we have set up the Job and the stored procedure that will execute that job, we move on to developing the actual SSRS report that will be used as the interface for business users to execute an ETL on their own. As I go through some of the items in the design view of the report, you can always access the complete report .rdl file in the Downloads section, located at the bottom of this article.

Visual Studio was used an IDE to develop this report and Figure 2 shows an SSDT 2013 design view of the report which consists of a single tablix control with 7 columns.


Figure 2: Sample Report in Design View

The tablix is populated by dataset – RunMyDummyJob. Figure 3 shows the properties of RunMyDummyJob dataset and you will notice that the dataset is in turn based off the RunMyDummyJob stored procedure which was created in the previous step (Step 2).

Figure 3: Dataset Properties

The dataset has a single parameter which is mapped to a report parameter – @Operation, as shown in Figure 4.

Figure 4: Report Parameter Mapping

As shown in Figure 5, the @Operation parameter has two hard-coded available values, namely, Refresh as well as Load. Refresh is set as the default value as a means of precaution so as not to execute the report every time it is opened/previewed.


Figure 5: Report Parameter Properties

By default, a preview of the report displays job activity information as shown in Figure 6.


Figure 6: Preview of the SSRS Report – Refresh

For a business users to execute the ETL (MyDummyJob), they will have to change the Operation parameter value from Refresh to Load and then click View Report button. Once the View Report button has been clicked, the report will display latest job activity details with the run status column indicating that the job is running as shown in Figure 7.


Figure 7: Preview of the SSRS Report – Load

In order to get the progress of the job execution, you will have to change the Operation parameter value from Load back to Refresh and click View Report button again. When the job has completed running, the report will display information similar to what is shown in Figure 8.


Figure 8

Another way of confirming that the job history displayed in the SSRS report is accurate is by viewing SQL Server Management Studio’s Job Activity Monitor and the Last run column should match to job start date field in the SSRS report.

Figure 9

Conclusion

Unlike self-service reporting, self-service ETL execution should not be a preferred way of running your ETL solutions as there are several steps and checks involved in getting it set up. However, in circumstances where a requirement for self-service ETL execution is justified, you can use the steps described in this article as a guide to assist you in setting up such a process.

Downloads

See more

For SSRS documentation, consider ApexSQL Doc, a tool that documents reports (*.rdl), shared datasets (*.rsd), shared data sources (*.rds) and projects (*.rptproj) from the file system and web services (native and SharePoint) in different output formats.

References

Sifiso W. Ndlovu

Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

He currently works for Clientele Life as an Assistant Manager in Business Software Solutions.

View all posts by Sifiso W. Ndlovu
Sifiso W. Ndlovu
Business Intelligence

About Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

He currently works for Clientele Life as an Assistant Manager in Business Software Solutions.

View all posts by Sifiso W. Ndlovu

2,682 Views