Run an SSRS subscription with SSIS

A quick post to see how to launch an SSRS subscription from SSIS.

It can be used as in my case for example to send by mail a report on the execution status of an SSIS package. Of course after inserting the logs of this package in a table, create a restitution report of these logs as well as a subscription which sends the report by email.

So how?

It's simple ☻, connect to the reporting services instance and run the following request to retrieve the id of the job that launches the subscription:

SELECT b.NAME AS JobName, a.SubscriptionID, e.NAME, e.Path, d.Description, d.LastStatus, d.EventType, d.LastRunTime, b.date_created, b.date_modified FROM ReportServer.dbo.ReportSchedule AS a INNER JOIN msdb.dbo.sysjobs AS b ON CAST (a.ScheduleID AS SYSNAME) = b.NAME INNER JOIN ReportServer.dbo.ReportSchedule AS c ON b.NAME = CAST (c.ScheduleID AS SYSNAME) INNER JOIN ReportServer.dbo. Subscriptions AS d ON c.SubscriptionID = d.SubscriptionID INNER JOIN ReportServer.dbo.CATALOG AS e ON d.Report_OID = e.ItemID WHERE e.NAME LIKE '% Monitoring Sales Integration%'

result:

In the SSIS package, add a Execute SQL Task.

We just have to copy the following code in the query editor by putting the recovered Job id:

EXEC [ServerName].msdb.dbo.sp_start_job 'EFA0F359-B968-4FF4-95B6-8DE0C60A9008'

It's done!