Trigger an Email of an SSRS Report from an SSIS Package

Question

Trigger an Email of an SSRS Report from an SSIS Package

Stan Kulp-439977

SSChampion

Points: 10062
More actions
March 19, 2013 at 12:03 am

#271789

Comments posted to this topic are about the item Trigger an Email of an SSRS Report from an SSIS Package
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

steve_kirchner SSC Veteran Points: 200 More actions · Answer 1

The sql job id from the subscription does not match the @eventdata parameter you inputed into your procedure execution statement. Shouldn't this match the guid from the sql job id? If not why and why obtain the sql job id?

dcdanoland SSChasing Mays Points: 647 More actions · Answer 2

This is a very interesting article. I once tried to do something like this with an earlier version of SQL Server with the web service API and .NET but was disappointed by the program's reliability. I got the sense that I was trying to do something that the product wasn't supposed to do. I like the fact that your solution appears to be running a stored procedure instead of the .NET API. However, I again wonder if it's still trying to do something that the product creators didn't intend.

Specifically, I have two concerns:

1. it seems like every subscription event that is added would need to be deleted as well or else you'd have lots of unneeded subscriptions on hand.

2. I wonder how robust and scalable the add subscription/remove subscription stored procedures are. What happens if two or more applications are trying to add or remove a subscription at the same time? Has that ever been tested by the product team? It seems like it's something that could break. I would want a lot of error handling around this solution.

0409SQL SSChasing Mays Points: 650 More actions · Answer 3

0409SQL

SSChasing Mays

Points: 650

March 19, 2013 at 5:36 am

#1598370

Really helpful !! Thanks a ton ! 😉

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 4

That's a typo. I started writing this, put aside for a couple of months, then made a few screen shots and finished it up. I didn't review it well enough. I will try to get one of the screen shots replaced.

This is what the screen shot of the "Enter SQL Query" box should look like:

http://i.imgur.com/SWzpXRa.png

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 5

1. You don't have to create a new subscription. If you have an existing subscription you can use that one.

2. You're not adding a subscription. You're adding an event to trigger an existing subscription.

keyser soze-308506 SSChasing Mays Points: 630 More actions · Answer 6

Hi

I need to send distinct report to distinct persons

I have a dataset of people, with their names, email, etc

including the parameters to run the report

Could I accomplish this with SSxS ?

regards

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Thanks, I am sure I will find this useful.

Nicely written as well 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dcdanoland SSChasing Mays Points: 647 More actions · Answer 8

I'm sorry I misunderstood the difference between a subscription and a subscription "event." I wish understood better what an "event" is with regard to SSRS subscriptions. I'm having trouble finding documentation on the AddEvent stored procedure. It seems that under "Subscription Processing Options" there are two options:

When the Scheduled Report Run is Complete

On a Shared Schedule

I interpret your example as using the first option, and that the AddEvent method is setting the schedule as if the "Select Schedule" button had been pressed and the user selected a date/time to process the report. If that's the case, then there is only ever one "When the Scheduled Report Run is Complete" event, and any time you run "AddEvent" it automatically deletes the previous schedule. I would expect that if you used a "Shared Schedule," the schedules would accumulate - but perhaps "AddEvent" doesn't pertain to shared schedules. Again, more documentation from Microsoft would be helpful.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

dcdanoland (3/19/2013)
I'm sorry I misunderstood the difference between a subscription and a subscription "event." I wish understood better what an "event" is with regard to SSRS subscriptions. I'm having trouble finding documentation on the AddEvent stored procedure. It seems that under "Subscription Processing Options" there are two options:
When the Scheduled Report Run is Complete
On a Shared Schedule
I interpret your example as using the first option, and that the AddEvent method is setting the schedule as if the "Select Schedule" button had been pressed and the user selected a date/time to process the report. If that's the case, then there is only ever one "When the Scheduled Report Run is Complete" event, and any time you run "AddEvent" it automatically deletes the previous schedule. I would expect that if you used a "Shared Schedule," the schedules would accumulate - but perhaps "AddEvent" doesn't pertain to shared schedules. Again, more documentation from Microsoft would be helpful.

I'm no expert, but that stored proc just does this:

insert into [Event]

([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])

values

(NewID(), @EventType, @EventData, GETUTCDATE(), NULL, NULL)

...and the Event table seems to be empty most of the time, so all it seems to be doing is telling ReportServer that an event happened.

(in this case pretending that a scheduled time has occurred)

It is not modifying / adding/ removing any schedules, just faking a timed event.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dcdanoland SSChasing Mays Points: 647 More actions · Answer 10

Mister Magoo,

Thanks for shedding some light on AddEvent. To be honest, I prefer my Oz mysterious and behind the curtain; I don't want to know how big of a sham it is. You have a database storage engine shrouded in complexities. I grasp Kalen Delaney's explanations on the same level as quantum mechanics - sort of, kind of, but not really. Then you have parts like of the Report Server that feel like they were hacked together by college juniors cramming on a take-home final. Don't get me wrong - Reporting Services is pretty solid now. But thorough documentation would make me a lot more willing to make database calls under the cover.

-Dan

rnjohnson10 SSC Journeyman Points: 96 More actions · Answer 11

I do this with shared schedules.

I've got about six shared schedules which are triggered at various times in my etl process.

I have a stored procedure which is triggered with a parameter indicating which shared schedule should be executed. The proc then reads the db to find the subscriptions associated with tht schedule and does this add event trick.

That way, we only have to assign a report to a shared subscription and it will go, no extra set up necessary.

It's been very stable for a couple of years now.

Honestly, this really shouldn't be so difficult a task to manage. It should be base functionality of the product to make ths process easy. No one dealing with etl processes is going to be able to schedule a report at a given time and be assured the data will be ready.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

Very useful article, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nahk.fussuy Ten Centuries Points: 1042 More actions · Answer 13

Nice article. I did a similar thing a few years ago using shared schedules. Avoids hard coding the GUID into stored procs.

I wrote it as one big mad post on the forums:

http://www.sqlservercentral.com/Forums/Topic576337-150-1.aspx

It describes how to setup shared schedules for the 'events' you want to trigger reports.

I then use a synonym to point to the report server. A new stored proc "runReportServerSchedule" is created that takes an 'event' name aka 'shared schedule' name.

Then all you do is subscribe reports using the shared schedule names and in your SSIS packages call the runReportServerSchedule with the schedule names as required. I used this in several systems with great success to allow users to get reports when data loads complete, fail, have validation problems etc.

Give it a read.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 14

Does this answer your question?

http://stackoverflow.com/questions/3471821/how-to-pass-a-parameter-to-ssrs-report-from-ssis-package