Trigger an Email of an SSRS Report from an SSIS Package

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.

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.

About Author:

Sameer Kothari works in Systems Plus and actively contributes to technology. To read more interesting articles from him , please follow: http://samk2010.blogspot.in/