Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Trigger an Email of an SSRS Report from an SSIS Package

By Stan Kulp,

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: 10205 | Views in the last 30 days: 107
 
Related Articles
FORUM

Script to execute the Reporting services Subscription

Reporting services Subscriptions

FORUM

Need One Click Button to Run All Reports

One Click Button to run all reports

FORUM

Report Subscription

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

FORUM

Executing multiple SSIS packages

Executing multiple SSIS packages

FORUM

Reporting Services driven DTS Package?!

Can you execute a DTS Package from within Reporting Services

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones