Calling SSRS Reports in SSIS (Export / Email)

Overview

This tutorial explains how to call SSRS reports from SSIS, export them to files (PDF, Excel, Word, HTML, and more), email them as attachments or as HTML in the message body, pass dynamic parameters, and automate delivery without relying only on fixed SSRS subscriptions. You will use the ZappySys Reporting Services Task (SSRS Report Generator) from SSIS PowerPack.

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

Why Automate SSRS Reports from SSIS?

SSRS subscriptions are useful for fixed schedules, but they can be limiting when you need event-driven report delivery. For example, you may want to send a batch of reports after an ETL load, data validation, billing process, or other workflow is finished. Standard SSRS subscriptions usually rely on hardcoded schedules and predefined parameters, so they are not always easy to invoke dynamically from a process or adjust at runtime. ZappySys SSRS Reporting Services Task helps solve this by letting SSIS control when reports run, which parameters are used, where files are saved, and how reports are emailed.

What You Will Learn

• How to call and export SSRS reports from an SSIS package.
• How to pass dynamic SSRS report parameters from SSIS variables.
• How to send SSRS reports by email as attachments.
• How to send a server SSRS report as an HTML email body (Email Settings tab checkbox or legacy two-step flow).
• How to automate report file names, paths, and post-processing steps.

SSRS Subscriptions vs. SSIS Automation

Tasks Used in This Tutorial

The examples below use these ZappySys SSIS PowerPack components:

	SSIS Report Generator Task (Export SSRS Reports)
	Advanced SSIS File System Task
	Send HTML Email Task

Video: Reporting Services Task in SSIS

This walkthrough demonstrates the SSRS Report Generator / Reporting Services Task in SSIS:

Export SSRS Reports from SSIS

First you will export a server-hosted SSRS report to a file. The task supports Word (.docx, .doc), Excel (.xlsx, .xls), PDF, TIFF, CSV, XML, and HTML (server reports).

Export SSRS Report to File (Word, Excel, PDF, CSV, HTML, XML)

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 your report service URL is, see Microsoft guidance for publishing reports to a report server (linked in the prerequisites section above).
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

Date and Time in the Export File Name

When you export SSRS reports from SSIS, you may want to add the current date or time to the report file name. For example, you can save the report as Filename20180323.xlsx, where 20180323 means March 23, 2018.

There are two ways to save a report file with date/time in the path:

1. New method: Use placeholder functions directly in the SSRS task Save As path under the Export Settings tab.
2. Legacy method: Export the report as a file first, and then rename the file using ZappySys Advanced File System Task.

The following legacy example shows how to add a date suffix after the report is exported.

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

Send SSRS Report as an Email Attachment

The Reporting Services Task can render the report and send it by email. You need SMTP settings (server, credentials, port). Follow these 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, on the Email Settings tab, choose New under SMTP connections to create a connection. Provide the SMTP server name, credentials, and port. You can find sample SMTP hosts and ports in this third-party list.
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>Second item in 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>Second item in 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, execute the Package and check your email received:
   

   Email received SSIS Html format

Send SSRS Report as HTML in the Email Body (Not as an Attachment)

By default, Output as email usually sends the rendered report as an attachment. To put the report inside the email as HTML, use one of the methods below.

Important: HTML export applies to server reports only. Local RDL mode does not support exporting as HTML; use a report deployed to SSRS.

Method 1: Email Settings Checkbox (Recommended, Newer Builds)

If your SSIS PowerPack build is from after May 6, 2026, use this single-step option.

In the Reporting Services Task, open the Email Settings tab and enable Use report output as Email body (*.htm / *.html export formats only). Configure SMTP and recipients as usual, and set the export format to HTML (.htm or .html). The task embeds the rendered report HTML in the email body—no separate Send HTML Email Task is required.

Email Settings: use report output as HTML email body (single-step)

Sample HTML Email (Embedded body with images)

Sending SSRS Report as Email embedded body (With image support)

Method 2: SSRS Task Plus Send HTML Email Task (Legacy)

Use this on builds on or before May 6, 2026, or when you want full control over the email HTML in Send HTML Email Task.

Summary of the two-step flow:

1. Reporting Services Task exports a server report to an HTML file (for example C:\temp\sales-order.html).
2. Send HTML Email Task reads that file into the message body using FUN_FILE_READ.

Legacy two-step flow: SSRS Task plus Send HTML Email Task

1. In the ZappySys Reporting Services Task, use Export server report, not local report mode.
2. Choose Output as file and export the SSRS report as an HTML file to the local system, for example C:\temp\sales-order.html.
3. Add ZappySys Send HTML Email Task in the next step of your SSIS package.
4. In the email body, use the FUN_FILE_READ placeholder function to read the HTML file exported in the previous step and paste the entire HTML content into the email body:

Pass SSRS Report Parameters from SSIS

Many SSRS reports define parameters (for example date range or customer ID). The Reporting Services Task can map SSIS variables to those SSRS parameters so values are chosen when the package runs. You need a report that includes at least one parameter to try this. To learn how to author parameters in SSRS, see Microsoft’s report parameters documentation.

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 Reporting Services Task in Visual Studio, go to Report Parameters and map the SSRS parameter with the SSIS variable:
   

   Map SSIS variable with SSRS parameters

Data-Driven Export for Multiple SSRS Reports

Data-driven mode lets you drive many report exports from a SQL Server configuration table: different reports, paths, email options, and parameters per row. The task can generate the sample table schema for you.

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

Export a Local SSRS RDL Report from SSIS

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 Connection String, 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

Move Exported Reports to a UNC Path

After export, you can copy or move files—for example to a shared folder via a UNC path—using Advanced File System Task.

1.  First of all, specify your file name to move. You can use *.xlsx 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

Related SSIS and SSRS Automation Scenarios

You can use this SSIS-based SSRS automation approach in many real-world workflows where a fixed SSRS subscription is not flexible enough:

• Send daily ETL validation reports only after the data load succeeds.
• Generate customer-specific reports by passing customer IDs or date ranges as parameters.
• Email invoice, billing, or sales reports after a business process is completed.
• Export audit reports to a shared folder with date/time in the file name.
• Run multiple SSRS reports from a configuration table and deliver each report to different recipients.

FAQ: SSRS Reports from SSIS

Can I call an SSRS report from SSIS?

Yes. You can use ZappySys SSRS Reporting Services Task inside an SSIS package to call a server SSRS report or a local RDL report and export it to formats such as PDF, Excel, Word, CSV, XML, TIFF, or HTML.

Can SSIS pass parameters to an SSRS report?

Yes. You can create SSIS variables and map them to SSRS report parameters in the Reporting Services Task. This is useful when report values such as customer, date range, region, or batch ID must be decided at runtime.

How do I send an SSRS report by email from SSIS?

You can use the Reporting Services Task to render the report and send it as an email attachment. For more advanced HTML email scenarios, export the report first and then use ZappySys Send HTML Email Task.

Can I send an SSRS report as the HTML body of an email?

Yes, but use server report mode. In newer builds (after May 6, 2026), open the Email Settings tab and check Use report output as Email body (*.htm / *.html export formats only), with an HTML export. On older versions, export the report to HTML and use Send HTML Email Task with a placeholder such as <<C:\temp\sales-order.html,FUN_FILE_READ>>.

Why use SSIS instead of SSRS subscriptions?

Use SSIS when the report must run after a workflow finishes, when parameters must be supplied dynamically, or when output file names, destinations, and email rules depend on package logic. SSRS subscriptions are better for simpler fixed schedules and predefined parameters.

Next Steps: Download and Try SSIS PowerPack

Automate SSRS from SSIS with the ZappySys Reporting Services Task—event-driven runs, dynamic parameters, flexible export paths, and richer email options than typical subscriptions alone.

• Download SSIS PowerPack — trial and licensed builds.
• SSRS Report Generator Task (Reporting Services Task) — product overview.
• SSIS PowerPack home — all tasks and components.

Conclusion

In summary, calling SSRS reports from SSIS is straightforward with the ZappySys Reporting Services Task. It fits scenarios where SSRS subscriptions are too rigid: jobs triggered after ETL or other processes, parameters chosen at runtime, and customized file naming or delivery. This guide covered export to file, email attachments, HTML-in-body email (new checkbox or legacy two-step flow), date/time file names, UNC moves, parameter mapping, data-driven batches, and local RDL export.

References

For more reading:

• Reporting Services Tutorial
  
• SSRS Report Generator Task in SSIS
• SSIS Send HTML Email Task
• Advanced SSIS File System Task
• Format Specifiers / Placeholder Functions