Every production ETL (Extract, Transform, Load) solution often requires a scheduling mechanism to execute the ETL process. In a SQL Server-based environment, SQL Server Agent is commonly used for scheduling tasks such as executing SQL Server Integration Service (SSIS) packages. However, there may be instances where business users need the ability to execute ETL solutions on-demand. In this article, we will explore how to deliver self-service ETL execution using SQL Server Reporting Services (SSRS) as a business interface.

The Notion of Self-Service ETL Execution

Typically, it is not recommended to allow business users to run ETL solutions directly. Instead, they should be given control over manipulating and reporting on the data. However, there may be valid circumstances that require extending this control to executing ETL solutions. In such cases, it is important to keep the process simple for business users. In our scenario, we leveraged the familiarity of business users with SSRS to provide a self-service ETL execution platform.

Step 1: Setup a SQL Agent Job

The first step involves setting up a SQL Server Agent job that will execute the ETL solution. If you already have a SQL Server Agent job for executing an ETL solution, you can skip this step. In our case, we created a sample SQL Server Agent job called “My Dummy Job”. The properties of this job can be configured according to your specific requirements.

Step 2: Configuring SSRS Dataset

Once the SQL Server Agent job is set up, we need to focus on SSRS development and deployment. In this step, we create a sample stored procedure called “RunMyDummyJob” that will be embedded in SSRS datasets. This stored procedure accepts a parameter called “@RunOption” which can have two values: “Refresh” and “Load”. The stored procedure checks if the job is currently executing and then executes the job based on the value of the “@RunOption” parameter.

Step 3: Report Development and Deployment

Now that we have set up the SQL Server Agent job and the stored procedure, we can proceed with developing the SSRS report that will serve as the interface for business users to execute the ETL on their own. The report can be designed using Visual Studio or SQL Server Data Tools (SSDT). The report should include a tablix control populated by the dataset “RunMyDummyJob”. The dataset has a parameter called “@Operation” which is mapped to a report parameter. The report parameter has two available values: “Refresh” and “Load”. By default, the report displays job activity information when opened or previewed. To execute the ETL, the business user needs to change the value of the “@Operation” parameter to “Load” and click the “View Report” button. The report will then display the latest job activity details.

Conclusion

While self-service reporting is a preferred way for business users to manipulate and report on data, self-service ETL execution should be approached with caution due to the complexity involved in setting it up. However, in certain justified circumstances, the steps outlined in this article can serve as a guide to help you establish a self-service ETL execution process using SQL Server Reporting Services (SSRS).