Generate and send report via email in PDF format which method better

Question

Generate and send report via email in PDF format which method better

Gergely Mészáros

SSC Veteran

Points: 270
More actions
October 4, 2013 at 3:10 am

#283979

Hi all,
I need some suggestions from experts, which way we should go.
Our company want to process and send various reports each week to a number of various recipients, and with various parameters. We have standard edition, so we don't have the opportunity to make data driven subscriptions.
Only our IT System Support department have sufficient rights to make subscriptions or to modify report definitions or the data sources of the reports. If we want to do such things, we have to get over a long process (approvals etc..).
The two ideas:
A.
Create data driven subscriptions wia stored procedure calls.
It must be done in two steps, because we dont have the rights to the reportserver db. So we put the subscription data somewhere, then a job creates the subscriptions from it.
B.
Load, render, save and send the reports via ReportExecutionService with an SSIS package.
similar to this: http://msbimentalist.wordpress.com/2011/12/27/execute-ssrs-report-from-ssis-package/
Feel free to suggest, write opinion, or form judgment!
Please consider security issues, possible load on support, maintanace costs.
Thanks in advance,
Greg

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Gergely Mészáros (10/4/2013)
If we want to do such things, we have to get over a long process (approvals etc..).

Greg, there's a reason for that long process and if you bypass it, you could get yourself and others fired. Work within the established system. If the system is "broken", then work to fix it. It'll take some time to do that and you'll have to get management buy-in, but it'll be worth it in the long run.

If you suggest there should be no such system, you will fail, so don't start there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gergely Mészáros SSC Veteran Points: 270 More actions · Answer 2

Hi Jeff,

Thanks for your reply. There were a little miscomprehension. I ve tried to write it in a sort paragraph, but it was too short.

It's a really strange situation. A bit more information:

IT System Support dont want to spend their time with managing subscriptions. And as i mentioned we have standard edition, no data driven subscription on the UI. They asked us to find a workaround. I surely wont be fired.

The question is wich workaround is less bad.

My opinion the A version is violence on the system. I would be happy, if someone wrote any detailed confirmation on it.

What i really want to know, what is the problem with the B version.

- Every responsible have the rights to run the reports.

- Therefore we have rights to the Report Execution Service.

- I think its one of the recommended interfaces of Reporting Services.

- I think doesnt matter SSIS or my IE requests the report.

- Version B has no essential difference from running the report, saving, and sending it. It is a way to do it easier.

Thanks,

Greg

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Ah... understood. I thought you were trying to "illegally" bypass established protocols of development in your company. Thanks for the clarification.

I believe this could be done in a rather simple manner if I'm reading the requirements correctly. I'm reading the requirements simply as there are several reports that need to be created and then certain reports need to be sent to certain groups of people.

I'm also reading that one of the biggest requirements is as you mentioned...

IT System Support dont want to spend their time with managing subscriptions.

I'm no SSRS nor SSIS expert so you'll need to figure out how to get either to save a file (I've been led to believe that's a simple task) but I believe that I'd do this as follows. I realize that this is just a functional overview but you have to start somewhere. As they say, you can't correct a blank piece of paper. 😉 As it turns out, this is a combination of both your "A" and "B" methods.

1. Create several nightly (or whatever schedule you need) jobs to create the reports as either PDF's or Spreadsheets depending on the need and have the system save them in a disk folder dedicated to the task.

2. Create a table that contains the report name, static file name, and the email address of each person to receive the report. There would be one row per report name/email address combination. It might also server you well to have a start and end date for each row so you have a history of who was enabled to receive which report and when. If you decide to do this (and I do recommend it), make the default end date '9999' which will be converted to 9999-01-01 for and end date. This will keep you from having to mess around with using NULL as an open end date. For reference purposes, I'd call this table something like 'ReportSubscription' (I almost never pluralize table names... I name tables after what a single row contains).

As a bit of a side bar, you should probably create more than just one table so that you can do a little normalization and establish some DRI (Declared Referential Integrity) for report and file names, etc. It's a minor complexity that almost always pays off in the long run.

3 The rest is fairly easy. Have a single job run a single stored procedure that that reads and finds the distinct values of all report names from the ReportSubscription table where "now" is between the start and end dates. Using the FOR XML PATH trick for concatenation, concatenate all of the "active" ("now" falls between the start and end dates) email addresses into the "to" list for each report, add the filename of the report for each report as a file attachment, and use all that to build the sp_senddbmail command for each report. Then execute each command. The construction of all of these commands can actually be done using a single well formed and fairly easy to construct SELECT to put all of these commands into a single variable and then just execute that one variable.

What all of that does for you is simple... it allows simple subscription management just by updating a small table or two. Therefor, privs need to only be granted to that small table or two. It will also keep a history of who was scheduled to get which report and when. Neither the stored procedure nor the job to send the emails will ever need to be changed. Only the content of the small table or two will need to be changed.

As yet another sidebar, you could also put a time to send on each report listing in a separate table and run the job several times a day. This would allow you to easily control multiple transmissions of the same (updated) report or when a single report was supposed to be sent. Of course, the jobs to create the reports would need to execute more than once per day if the report needed to be sent more than once per day.

The advantage of storing the reports in a dedicated disk folder is that if someone needed to see the report on an ad hoc rather than scheduled basis, they could simply retrieve it instead of needing privs to run the report creation job and the email job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

p.s. I forgot one caveat of sp_Send_DBMail... the default size of any attachment is only 1MB. Please lookup sp_Send_DBMail in "Books Online" and look for @file_attachments. according to BoL, someone will have to make the onetime effort to run the "Database Mail Configuration Wizard" to change that default.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Amit Raut SSCrazy Points: 2613 More actions · Answer 5

Thanks Jeff! That's indeed a valuable information.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

Thanks, Amit. Please let us know how it works out for you. As a bit of a sidebar, I'm sometimes a little amazed at how tightly some folks lock down reporting servers internally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)