Calling SSRS Reports in SSIS (Export / Email)

Introduction about calling SSRS Reports in SSIS

Calling 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:

• • Create a Basic Table Report (SSRS Tutorial)
  • Publishing Reports to a Report Server

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:
   

   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:
   

   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:
   

   Export SSRS report using SSIS to Excel, Word, PDF

7. Finally, run the package and the file will be created:
   

   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
   

   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:
   

   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:
   

   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:
   

   SSIS move files to UNC path

3. Also, It is possible to sort files by name, size, modified date or other 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:
   

   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:
   

   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:
   
   XHTML

   <h1>This is an example using headers</h1> <p><strong>This is an example to write using bold</strong> <strong><br /> </strong><em>This is an example to write using italic <br /></em> This is an example using underline<br /><br /> </p> <ol> <li>This is an example with lists</li> <li>Sedond of the list</li> <li>Third of the list</li> </ol> <ul style="list-style-type:circle"> <li>This is an example with bullets</li> <li>Bullet sample </li> <li>Working with bullets</li> </ul>

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   <h1>This is an example using headers</h1> <p><strong>This is an example to write using bold</strong> <strong><br /> </strong><em>This is an example to write using italic <br /></em> This is an example using underline<br /><br /> </p> <ol>   <li>This is an example with lists</li>   <li>Sedond of the list</li>   <li>Third of the list</li> </ol> <ul style="list-style-type:circle">   <li>This is an example with bullets</li>   <li>Bullet sample </li>   <li>Working with bullets</li> </ul>

    
   

   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:
   

   Using SSIS variables in emails

5. Finally, run the script and check your email received:
   

   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:
   

   Reporting Service Parameter

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

   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:
   

   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:
   

   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:
   
   Transact-SQL

   CREATE TABLE [{{SchemaName}}].[{{TableName}}]( Id int identity(1,1) NOT NULL PRIMARY KEY, GroupName varchar(100) NULL, /* Used to filter reports */ ReportType varchar(10) NOT NULL, /*Local or Server*/ ReportServiceUrl varchar(500) null, ReportPath varchar(500) NOT NULL, IsEnabled bit NOT NULL default(1), ExportAsFile bit NOT NULL default(1), ExportAsEmail bit NOT NULL default(0), Parameters varchar(max) NULL, /* ParameterName1;IsMultiValue;Value1|....ParameterNameN;IsMultiValue;ValueN */ ExportPath varchar(500) NULL, AddFileSuffix bit NULL, ExportFileSuffix varchar(500) NULL, /* e.g. _ddMMyyyy, _ddMMyyyyhhmmss, _ddMMyyyyhhmmss.fff (see this URL http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.110).aspx) */ Description varchar(500) NULL, EmailFrom varchar(255) NULL, EmailTo varchar(1000) NULL, EmailCc varchar(1000) NULL, EmailBcc varchar(1000) NULL, EmailSubject varchar(500) NULL, EmailIsHtml bit NULL default(1), EmailBodyText varchar(max) NULL, EmailAttachmentFile varchar(500) NULL, /*Unused: For future use only*/ LastExportStartDate datetime NULL, LastExportEndDate datetime NULL, LastStatus varchar(20) NULL, LastMessage varchar(max) NULL, Datasets varchar(max) NULL, /*if ReportType=local and report file is using dataset(s) then supply data in this column (multiple values must be pipe delimited). e.g. Dataset1|Dataset2)*/ DatasetConnectionStrings varchar(max) NULL, /*if ReportType=local and report file is using dataset(s) then supply data in this column (connection string must be same order as datasets (multiple values must be pipe delimited) enter just one connectionstring if all datasets using same connection. ConnectionString must be ADO.NET for SQLServer, ODBC or OLEDB connection string and correct driver must be installed on the system)*/ DatasetQueries varchar(max) NULL /*if ReportType=local and report file is using dataset(s) then supply data in this column (queries must be in the same order as dataset(s) listed in Datasets column. enter multiple queries in this format <xml><query>{query for 1st dataset}</query><query>{query for 2nd dataset}</query></xml>)*/ );

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

   CREATE TABLE [{{SchemaName}}].[{{TableName}}]( Id int identity(1,1) NOT NULL  PRIMARY KEY, GroupName varchar(100) NULL,  /* Used to filter reports */ ReportType varchar(10) NOT NULL, /*Local or Server*/ ReportServiceUrl  varchar(500) null, ReportPath varchar(500) NOT NULL, IsEnabled bit NOT NULL default(1), ExportAsFile bit NOT NULL default(1), ExportAsEmail bit NOT NULL default(0), Parameters varchar(max) NULL, /* ParameterName1;IsMultiValue;Value1|....ParameterNameN;IsMultiValue;ValueN */ ExportPath varchar(500) NULL, AddFileSuffix bit NULL, ExportFileSuffix varchar(500) NULL, /* e.g. _ddMMyyyy, _ddMMyyyyhhmmss, _ddMMyyyyhhmmss.fff (see this URL http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.110).aspx) */ Description varchar(500) NULL, EmailFrom varchar(255) NULL, EmailTo varchar(1000) NULL, EmailCc varchar(1000) NULL, EmailBcc varchar(1000) NULL, EmailSubject varchar(500) NULL, EmailIsHtml bit NULL default(1), EmailBodyText varchar(max) NULL, EmailAttachmentFile varchar(500) NULL, /*Unused: For future use only*/ LastExportStartDate datetime NULL, LastExportEndDate datetime NULL, LastStatus varchar(20) NULL, LastMessage varchar(max) NULL, Datasets varchar(max) NULL, /*if ReportType=local and report file is using dataset(s) then supply data in this column (multiple values must be pipe delimited). e.g. Dataset1|Dataset2)*/ DatasetConnectionStrings varchar(max) NULL, /*if ReportType=local and report file is using dataset(s) then supply data in this column (connection string must be same order as datasets (multiple values must be pipe delimited) enter just one connectionstring if all datasets using same connection. ConnectionString must be ADO.NET for SQLServer, ODBC or OLEDB connection string and correct driver must be installed on the system)*/ DatasetQueries varchar(max) NULL /*if ReportType=local and report file is using dataset(s) then supply data in this column (queries must be in the same order as dataset(s) listed in Datasets column. enter multiple queries in this format <xml><query>{query for 1st dataset}</query><query>{query for 2nd dataset}</query></xml>)*/ );

    
   

   Sample Database in SSIS for SSRS

5. Finally, you can check the table with the configuration:
   

   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:
   

   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:

• Reporting Services Tutorial
  
• SSRS Task in SSIS