Run an SSRS subscription with SSIS

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

It can be used in this case to send by email a report on the execution state of an SSIS package. Of course, after inserting the logs of this package into a table, create a SSRS report rendering these logs as well as a subscription that sends the report by mail.

So how do we do that?

It's simple, log on to the service reporting instance and run the following query to retrieve the id of the job that is launching 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 an Execute SQL Task:

We just have to copy the following code in the query editor by putting the job id retrieved.

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

It's done!