There are situations in which it would be useful to save an SSRS report to a network folder when an underlying table is updated by an SSIS package. This can be accomplished by creating a data-driven subscription for 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 walk-through we will presume that the SSIS package and SSRS report already exist, and that the report has been published to SQL Server Reporting Services.

Create a data-driven subscription

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

Open the Subscriptions panel of the editor.

Click on the New Data-driven Subscription link.

Enter a descripton and select Windows File Share as the method of delivery and click the Next button.

Enter the appropriate conection information and click the Next button.

Enter the following SQL command...

DECLARE @DateTimeString AS VARCHAR(15) = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':',''))
SELECT 
'TestReport-' +  @DateTimeString  AS FileName,
'PDF' AS FileFormat,
'\\TestServer\DestinationFolder' AS Folder

...that selects the name of the report, file format and UNC network folder name (substituting your file name, desired format and the name of your network folder - you can also select them from a parameters table) where the file is to be saved...

...into the text box and click the Next button.

Set the file name, path and render format fields as "Get the value from the database" and choose the appropriate values, enter a valid user name and pasword and click the Next button.

If your report has parameters, set the default parameters and click the Next button.

Choose the "On a schedule created for this option" radio button and click the Finish button.

Choose the Once radio button and choose a "Begin running this schedule on" date in the past so the subscription never actually runs on a schedule.

Capture the Subscription ID of the data-driven subscription

Click the Edit link of the new subscription.

Copy the SubscriptionID from the URL of the subscription...

...and paste it into Notepad for safekeeping.

Add an Execute SQL Task component to the SSS package

Drag-and-drop an Execute SQL Task component into the SSIS package from which you wish to run the data-driven subscription.

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

Configure the connection and add SQL code to the Execute SQL Task component

Double-click on the "Archive report" task component to bring up the Execute SQL Task Editor.

Click on the Connection drop-down list and select the correct database connection manager.

Click on the SQLStatement menu button ...

...to bring up the Enter SQL Query text editor.

Paste the following code into the query editor...

EXEC ReportServer.ado.AddEvent @EventType='TimedSubscription',@EventData='31784d36-c544-4224-894c-a0de1d61a000'

...substituting the Subscription ID you captured to Notepad for the example Subscription ID.

Click the OK buttons on the Enter SQL Query editor and the Execute SQL Task editor and save the SSIS package.

Execute the package and check for the archived SSRS report

The next time the SSIS Package is run the report will be generated and saved to the folder designated by the data-driven subscription.

The date and time strings embedded in the report file name prevent previous copies of the report from being overwritten.

Conclusion

In this walk-through we created a data-driven subscription for an SSRS report, captured its subscription ID, added an Execute SQL Task component to an SSIS package and inserted SQL code that would execute the subscription when the SSIS package was excecuted.