Microsoft SQL: Schedule SSRS

Owned by Former user (Deleted)
Last updated: Apr 17, 2023 by Stonebranch (Deactivated)

Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at https://www.stonebranch.com/integration-hub/Terms-and-Privacy/Terms-of-Use/.

Overview

This document provides documentation on how to install and use the Universal Task for SQL Server Reporting Services.

The Universal Task described here calls a script to deploy a report on the Reporting Services Webserver.

This Universal Task is based on the SQL Server Reporting Services RS.EXE utility. RS.EXE is a command line utility that can perform many
scripted operations related to SQL Server Reporting Services (SSRS). It can be used to complete various administrative tasks including
publishing reports and moving reports from one server to another server. The RS.exe utility requires an input file to tell the RS.EXE
"what to do". The list of actual tasks that can be performed includes, among others:

  1. Deploying / publishing reports
  2. Moving reports
  3. Exporting reports to a file
  4. Adjust security
  5. Cancel a running job
  6. Configure SSRS system properties

The Universal Task described here calls a script to deploy a report (#1 in the list above). Points 2-6 in the list above can also be implemented via a script called by the Universal Task.

Software Requirements

Software Requirements for Universal Agent

  • Universal Agent for Linux or Windows Version 7.0.0.0 or later is required.

Software Requirements for Universal Controller

  • Universal Controller 7.0.0.0 or later is required.

Import the Microsoft SQL: Schedule SSRS Universal Template

To use the Microsoft SQL: Schedule SSRS Template, you first must perform the following steps:

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true. For more information about Resolvable Credentials click here.

  2. Download the provided ZIP file from the Stonebranch Integration hub.

  3. In the Universal Controller UI, select Administration > Configuration > Universal Templates to display the current list of Universal Templates.

  4. Click Import Template.

  5. Select the template ZIP file and Import.

When the template has been imported successfully, the Universal Template will appear on the list. Refresh your Navigation Tree to see these tasks in the Automation Center Menu.

System Set-up

Universal Task name: Microsoft SQL: Schedule SSRS 

Software used:

  • MS SQLSERVER 2016
  • Report Builder in SQLSERVER 2016
  • Windows Server 2012R2
  • Universal Agent has been installed on the SAP Data Services Server

Proposed enhancements

A) A script for all possible RS.EXE scenarios should be generated

  1. Deploying / publishing reports (done)
  2. Moving reports
  3. Exporting reports to a file
  4. Adjust security
  5. Cancel a running job
  6. Configure SSRS system properties

B) Deploy reports using a SOAP Webservice

SQLSERVER allows deploying reports via SOAP Webservice. This solution has the advantage that no Universal Agent would have to be deployed on the SQLSERVER.

Solution Description

The following describes how the Universal Task can be demonstrated, once it is set up on the Stonebranch demo system.

Configure a new Report using Report Builder in SQLSERVER 2016

The following screenshot shows the MS Report Builder home screen.

The report configuration is written to the file system as an *rdl file. For example, rechnungsdaten.rdl.

Test the Report in Report Builder in SQLSERVER 2016

The following screenshot shows a sample MS Report Builder Report.

Once it is tested in Report Builder in SQL Server 2016, you can deploy the report to the Reporting Server using the Universal Task.

Universal Template

The following screenshot shows the Universal Template upon which integrations for the Report Builder is based on.

The following screenshot shows the Universal Template Script, which is called to deploy a report in the MS Reporting Server.

The following screenshot shows the Universal Template fields, which are provided in the Universal Task. The field values are executed as variables in the universal template script.

Universal Task

Configure the Universal Task to deploy a Report to the Reporting Server.

The Universal Task parameters are described in the following table.

ParameterDescription
REPORTNAMEThe actual name of the report (=report file) to be deployed.
REPORTSERVER_FOLDERThe folder where the file should be deployed to (reportingserver webfolder).
FILEPATH

The location of the rdl file.

Verify job execution in SAP Data Service Management Console

Log-on to the Reporting Webserver and check that the report has been deployed (see Universal Task Parameter reportserver).

For example, http://walldorf/Reports/browse/ (user:admin/xxx)

Browse to the folder where the report should be deployed (see Universal Task Parameter REPORTSERVER_FOLDER). For example, /myreports.