Trigger a Report Subscription from SSIS

1
votes

Is it possible to trigger a SSRS report subscription by using a SSIS package?

This package when invoked will trigger a subscription of the report, and email the data extract as an attachment to an email?

To expand on this,

I am planning to execute this script:

update dbo.Subscriptions

set MatchData = Convert(ntext,'' + Convert(nvarchar(max),dateadd(minute, 3, getdate()),126) +'+00:00' + '1truetruetruetruetruetruetrue')

where Report_OID = '51C956E3-3C62-4BAF-B3E4-8CBC2AA1DCD5'

which will re-schedule the subscription by 3 mins from getdate()

However, even though the data is updated and the report subscription is reflecting this, no email is being triggered as it ideally should :(

Am I messing up with the XML formatting as the column Matchdata seems to be of ntext type?

2
reporting-servicesssis

2 Answers

5
votes

Absolutely; SSRS subscriptions are just SQL Server jobs, which you can easily trigger via SSIS. See here for one example of finding your subscriptions (The default job names are not very friendly):

http://gallery.technet.microsoft.com/scriptcenter/List-all-SSRS-subscriptions-968ae4d5

2
votes

i usually do this via the ReportServer.dbo.AddEvent procedure, here's a sample call

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='9c9b80e9-72ca-446b-b6b8-27604f72b8fb'

The @EventData part you can get from the Subscriptions table