How to make data driven subscription using stored procedure.

Tested with SQL Server 2012

What is data driven subscription?

Suppose if we want to deliver an SSRS report to a particular user every day at 6 Am in the morning, we can setup a subscription. Suppose we need to send the same report to all department heads (say 5 people) each one should get the report in pdf which is rendered with their own dept code passed as parameters. This we can achieve by setting up 5 different subscriptions each one with each dept code as parameter and dept head’s email id as subscription email. But what will happen if we need to send the report to 1000 customers each one with different parameter and to different email id. We can use data driven subscription in this case. We can have a table which contains parameter( s ) and email id. Report has to use the information from table dynamically. 

Why do we need TSQL for data driven subscription ?

SSRS does have an inbuilt data driven subscription which is available only on Enterprise edition of SQL Server . This is a valid reason for using data driven subscription using TSQL. Another reason for using this is tracking the delivery. If we have few thousands of mail delivery and we may need to track the outgoing mails, this would be the best solution as we may be able to track the mails delivered and it is possible to resend if it got broken in between. Another reason for using this everything can be encapsulate in SQL server and we will have full control over it. Also we can initiate notification if a delivery fails.  Also this can be used to keep a decent log of actions for each email delivery.

See the specs on how to do it.

1. Create a table with columns for one or more parameter and ToEmail id. You can customise it by addint CCEmail, BCCEmail etc. And each email id can be single email or multiple email id seperated by ;. Lets call this table as tblSubscribers

2. Create the report and publish. Create a subscription of the report with any dummy parameter and email id. No need to schedule it. Once you create the subscription, an sql agent job will be created automatically. You can get the subscription ID by querying like this in the ReportServer database:

SELET cat.Name AS SSRSReport,rsd.ScheduleID

FROM Catalog cat

INNER JOIN Subscriptions sub ON cat.ItemID = sub.Report_OID

INNER JOIN ReportSchedule rsd ON cat.ItemID = rsd.ReportID

AND rsd.SubscriptionID = sub.SubscriptionID Where cat.Name = ‘<SubscriptionName>’

3. Create a stored procedure usp_Subscription which will have the following steps

a) Table SubscriptionID from ReportSchedule using the scheduleID we got already

b)Get into a loop or cursor looping through each row in the table tblSubscribers

c)In the Subscriptions table there is a field called extension settings which is an XML value which contain few inputs including ToEmail id. Either replace that ToEmail there or create another xml with our own value and update that to the table

d)There is another field Parameters in the same Subscriptions which has to be replaced with our parameter value from the table

e)call the job msdb..sp_start_job with parameter as scheduleID

f) do a wait if necessory

g) add the logging code here. You can take the log status from LastStatus column from the subscriptions table

e) end loop