I have an SSRS report that accepts a parameter, based on this parameter the report should be sent in an email body to a specific address. There can be hundreds of different addresses that these can be sent to. We are using SQL Server Standard Edition.
Based on this, the following is true:
- We cannot use data driven subscriptions (Only available in Enterprise edition)
- We cannot set up multiple subscriptions for each potential recipient (There could be hundreds)
- We do not want to send the report as an attachment (Needs to be the body of the email)
I know we can call a subscription from SQL Server or via .Net code, but we cannot change the recipient as far as I can see. Our best solution at the minute is to create a HTML string, outside of SSRS, with the appropriate formatting and add that to the body of an email. In doing this it means that a developer needs to be on hand to create the string if anything needs changed, or if a new report needs created, so it is not very flexible for anyone not familiar with HTML.
So is there any other way to create a report in SSRS and send it in the body of an email to a specified email address, based on a parameter value?
Best Answer
The short answer is yes, but its not simple. Here's something I developed for SQL 2008.
Firstly to make the report appear in the email body you just need to output it using the MHTML renderer. This can be parameterised too.
Next you'll need an SSIS package with a script task that can run the report and produce the required output.
Here's a snippet of the VB script you'll need with SSIS:
(Trolls forgive me for using VB. I'd only ever use C# these days)
First method for saving the file.
Second method to call the SSRS report in the required format and save using the first method.
You'll need to use SSIS package variables to handle how the report is produced, in what format and from where.
Then I created a stored procedure to call the SSIS package with the values needed. This then used the SQL Server database mail to collect the SSIS produced file, attach it and off you go with recipients handled by the dbmail rather than an SMTP call from SSRS subscriptions.
Here's a the procedure.
This gives you complete flexibility way to run any SSRS report and send it to whoever. But it is a lot of effort and over engineering to achieve a workaround to the current inflexible out of the box functionality.
Lastly I'd recommend iterating over a config table contains email address data etc using the procedure above.
Of course other bespoke report parameters can be added to with this approach if you want.