Trigger an Email of an SSRS Report from an SSIS Package

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Comments posted to this topic are about the item Trigger an Email of an SSRS Report from an SSIS Package

  • steve_kirchner

    SSC Enthusiast

    Points: 190

    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

    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

    Really helpful !! Thanks a ton ! 😉

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    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

    SSCrazy Eights

    Points: 9977

    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: 600

    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: 47068

    Thanks, I am sure I will find this useful.

    Nicely written as well 🙂

    MM



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

  • 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]

  • dcdanoland

    SSChasing Mays

    Points: 647

    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: 47068

    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);

  • 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]

  • dcdanoland

    SSChasing Mays

    Points: 647

    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

    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: 258965

    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

    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

    SSCrazy Eights

    Points: 9977

    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 34 total)

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