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.
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);
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
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.
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
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.
Does this answer your question?
http://stackoverflow.com/questions/3471821/how-to-pass-a-parameter-to-ssrs-report-from-ssis-package
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply