SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Trigger an Email of an SSRS Report from an SSIS Package

By Stan Kulp, (first published: 2013/03/19)

There are situations in which it would be useful to send a report to interested parties when an underlying table is updated by an SSIS package. This can be accomplished by creating a subscription to the report and triggering the subscription from the SSIS package via SQL Server Agent and the ReportServer.dbo.AddEvent stored procedure.

For the purposes of this tutorial, we will presume that the SSIS package and SSRS report already exist, and that the report has been published to SQL Server Reporting Services.

Step 1: Create an SSRS subscription

Enter the edit mode of the report from SQL Server Reporting Services.

Open the "Subscriptions" panel of the editor.

Click on the "New Subscription" node.

Add the "To" email address(es)  and choose the desired "Render Format," then click on the "Select Schedule" button.

Set the report to run only once, and change the beginning and end dates to dates that have already passed (the end date must be greater than the start date).

Creation of the new subscription is complete.

Step 2: Determine the SQL Server Agent Job ID of the new subscription from its URL in "Edit" mode

Click on the "Edit" node of the above screen and search for the SubscriptionID string in the URL as highlighted below.

Copy and paste the SubscriptionID to Notepad for safekeeping.

Step 3: Add an Execute SQL Task component to the SSIS package

Drag-and-drop an Execute SQL Task component into the SSIS package.

Add a precedence constraint to the Execute SQL Task and change its label to "Trigger report."

Step 4: Add the execute procedure SQL code to the Execute SQL Task component

Double-click on the "Trigger report" task to bring up the edit screen.

Click on the button and add the following code, pasting in the SubscriptionID from Notepad.

Click the OK button and save the SSIS package.

The next time the package is run, the report will be sent to the subscription recipients.

Total article views: 18806 | Views in the last 30 days: 8
Related Articles

Execute a data-driven SSRS subscription from SSIS to archive a report

This article demonstrates how to generate and save an SSRS report to a network folder when an SSIS p...


Script to execute the Reporting services Subscription

Reporting services Subscriptions


SSRS report subscription: Fire Event from SSIS package

My previous two posts about SSRS reports being deployed to a SharePoint site along with their subscr...


Report Subscription

Creating subscription using C# code (Using Report server Webservices)


Need One Click Button to Run All Reports

One Click Button to run all reports