Calling SSRS Reports in SSIS (Export / Email)

Introduction about calling SSRS Reports in SSIS

Logo of Reporting ServicesCalling SSRS Reports in SSIS is straightforward using the ZappySys Reporting Services task. Also, we will show how to export files SSRS files using SSIS, how to send reports in emails and how to send parameters.

In addition, we will be using this ZappySys SSIS PowerPack component to make things work:

In order to understand the functionality, the following video tutorial can be useful to understand the functionality of this useful Reporting Services task:
[youtube https://www.youtube.com/watch?v=ajnSgbXls7I&w=560&h=315]

Requirements

  1. First of all, we will need ZappySys SSIS PowerPack. This tool contains the Reporting Services Task.
  2. Secondly, you will require SSDT for Business Intelligence to create SSIS projects.
  3. Also, make sure to have an SSRS report deployed. For more information
    about creating reports and deploying, refer to these links:

Getting started

We will first show how to export an SSRS Report to Word format (docx). Also, the ZappySys SSIS PowerPack, allows converting your SSRS reports deployed into Word (docx and doc), Excel (xlsx and xls), PDF, TIFF, CSV and XML.

Calling SSRS Reports in SSIS

  1. First of all drag and drop the ZS Reporting Task that is included with our ZappySys SSIS PowerPack:
    Task to convert SSRS report into files

    SSRS task in SSIS

  2. Secondly, in general tab, make sure that the Export server report is enabled.
  3. Additionally, specify the report service URL. If you are not sure what is your report service URL, check the step 3 in requirements.
  4. In addition, in report path, select the report that you want to export to a file.
  5. Also, check the output as a file option:
    Configurations to export files

    Export report to file

  6. Additionally, in the export settings tab, go to Save as and specify the path and extension of the file to export. You can export to Excel, Word, PDF, Tiff, CSV and XML:
    Reporting Services to Excel Word PDF

    Export SSRS report using SSIS to Excel, Word, PDF

  7. Finally, run the package and the file will be created:
    Calling SSRS Reports in SSIS to export to MS Word

    SSRS report exported to docx

Changing the names of the reports with the date format

In order to explain how to add the prefix or suffix of a file with the current date. For example, I want my SSRS reports with this format:
Filename20180323.xlsx where 20180323 means march 23, 2018. To understand how to do it, In this new example, we will show how to do it.

  1. First of all, we will use the ZappySys Advanced File System Task:
  2. Secondly, we will use the option Rename files and rename using the fun_getdate function like this: Filename<<yyyy-MM-dd,FUN_GETDATE>>.xlsx
    SSIS Rename files with date

    Rename the file with a date in SSIS

  3. Also, you can watch all the available examples of functions by pressing the Expressions (x) button:
    Functions in SSIS

    Special SSIS functions

  4. Finally, press insert variable and function examples and you will be able to see all the examples of special functions to simplify your life:
    Special functions

    Samples of functions

Moving your report files to a UNC path

Once that you have your files exported from reporting services to a file, you can move, copy them to another path. In this example, we will show how to move files to a Shared folder using a UNC path.

  1.  First of all, specify your file name to move. You can use *.xml for example to move all the files.
  2. Secondly, specify the UNC path in destination:
    Move SSRS reports exported to UNC path

    SSIS move files to UNC path

  3. Also, It is possible to sort files by name, size, modified date or other properties:
    SSIS Sorting files by properties

    Sort file in SSIS by name, size, modified date

  4. Finally, press the Examples link, you can see some custom queries that you can create:
    SSIS queries to get file information

    Sample SSIS queries to files

Sending SSRS Report as an attachment in an email

The task also allows calling SSRS reports in SSIS and send the reports to an email. To do it, it is necessary to have an SMTP Server. Let’s take a look at the steps:

  1. First of all, in the general tab, check the email report file and uncheck the Output as file option:
    Send SSRS report as attachment in SSIS

    Output SSRS report in SSIS as attachment

  2. Also, in Email setting tab, press New in SMTP connections to create a new connection. You will need to provide an SMTP server name, an email and a password. It is also necessary to enter the port. You can find the list of some SMTP servers and ports here.
  3. In addition, you have an option to specify the sender, receiver, subject, and body. It is possible to write the body in HTML format. You can also specify SSIS variables. The following HTML code shows how to write in bold,
    italic, with bullets and more:
     
    SSIS HTML mail message

    Dynamic HTML in SSIS

  4. Additionally, in Visual Studio go to the menu and SSIS, Variables and create two variables that will be used in the report named Title and ReportName of type string and specify values for them:
    SSIS dynamic emails

    Using SSIS variables in emails

  5. Finally, run the script and check your email received:
    The email with the SSRS Report

    Email received SSIS Html format

Calling SSRS Reports in SSIS with parameters

Another key point is the use of parameters. Sometimes your SSRS includes parameters. With our Reporting Service Task, it is possible to call an SSRS report and pass SSIS parameters. You will require a Report with a parameter to test it. If you do not know how to create a parameter in SSRS, click here.

  1. First of all, you will need an SSRS Report with a parameter:
    SSRS reporting with parameters

    Reporting Service Parameter

  2. Secondly, in SSDT or Visual Studio, in the menu, go to SSIS and variables. Create an SSIS variable:
    Creating variables in SSIS

    Variables in SSIS

  3.  Finally, in Report Services Task in Visual Studio, go to Report Parameters and map the SSRS parameter with the SSIS variable:
    SSIS mapping of SSRS paremeters and SSIS variables

    Map SSIS variable with SSRS parameters

Calling SSRS Reports in SSIS using data-driven configuration

Data-driven configuration allows configuring different reports with different configurations. In addition, the option allows handling multiple reports using a table with all the configurations for each report. Also, this task creates a table in SQL Server with the configurations for each report. Let’s take a look to see how this works.

  1. First of all, select the data-driving report export using config table.
  2.  Also, you will need to specify the connection to the SQL Server
    database.
  3. In addition, go to config table, press new to create a new table:
    Data-driven configuration for SSRS in SSIS

    SSRS Data-driven in SSIS

  4. Additionally, ZappySys allows creating a sample table that can help you to understand how to configure local reports, Calling SSRS Reports in SSIS and save to files, specify paths, ports, email options and more:
     
    Data-driven table in SSRS

    Sample Database in SSIS for SSRS

  5. Finally, you can check the table with the configuration:
    SSIS data-driven table in SSRS

    Data-driven table created in SSIS

Calling SSRS Reports in SSIS to Export local report option

With local reports, you can work with rdl files. The rdl (Report Definition Language) files are part of the SSRS. They are physical files that contain the report format and other components. Using the export local report you can customize queries and see the results as reports. Here we will explain how to do it.

  1. First of all, in the Reporting Services task in Visual Studio, make sure to select the option Export local report.
  2. Secondly, in report path, you need to specify your rdl file path.
  3. In addition, check the dataset. The dataset is usually detected by default. It is included in the SSRS.
  4. Also, in Dataset Constr, you will need to specify the connection information. You need to specify the SQL Server name, Database name, Authentication type. For more information about connection string, click here.
  5. Finally, in dataset queries, you need to specify your query using the XML and query tags:
    SSRS report in SSIS

    SSIS local SSRS report

Conclusion

In summary, calling SSRS Reports in SSIS is a straightforward task using the ZappySys Reporting Service task. In this article, we learned how to export a Reporting services Report to a file. Also, we learned how to send emails using SSIS with the report attached and we also learned how to rename, copy files and finally how to use Data-Driven configuration and create queries to local reports. If you liked the tool, you can download it here.

References

Finally, refer to these links for more information:

Posted in SSIS Advanced File System Task, SSIS Report Generator (SSRS) and tagged , , .