Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Trigger an Email of an SSRS Report from an SSIS Package Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 12:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 118, Visits: 838
Comments posted to this topic are about the item Trigger an Email of an SSRS Report from an SSIS Package
Post #1432480
Posted Tuesday, March 19, 2013 2:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:24 AM
Points: 12, Visits: 119
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?
Post #1432515
Posted Tuesday, March 19, 2013 4:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:41 AM
Points: 25, Visits: 128
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.
Post #1432562
Posted Tuesday, March 19, 2013 5:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
Really helpful !! Thanks a ton !
Post #1432580
Posted Tuesday, March 19, 2013 6:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 118, Visits: 838
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
Post #1432598
Posted Tuesday, March 19, 2013 6:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 118, Visits: 838
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.
Post #1432600
Posted Tuesday, March 19, 2013 6:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:03 PM
Points: 163, Visits: 113
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

Post #1432601
Posted Tuesday, March 19, 2013 7:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 1,651, Visits: 5,197
Thanks, I am sure I will find this useful.

Nicely written as well


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1432642
    Posted Tuesday, March 19, 2013 7:48 AM


    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Tuesday, March 25, 2014 11:41 AM
    Points: 25, Visits: 128
    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.
    Post #1432653
    Posted Tuesday, March 19, 2013 9:29 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 12:35 PM
    Points: 1,651, Visits: 5,197
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1432737
    « Prev Topic | Next Topic »

    Add to briefcase 123»»»

    Permissions Expand / Collapse