Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger an Email of an SSRS Report from an SSIS Package


Trigger an Email of an SSRS Report from an SSIS Package

Author
Message
Stan Kulp-439977
Stan Kulp-439977
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 1106
Comments posted to this topic are about the item Trigger an Email of an SSRS Report from an SSIS Package
steve_kirchner
steve_kirchner
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 129
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
dcdanoland
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 166
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
0409SQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 109
Really helpful !! Thanks a ton ! ;-)
Stan Kulp-439977
Stan Kulp-439977
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 1106
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
Stan Kulp-439977
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 1106
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
keyser soze-308506
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 125
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
mister.magoo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2653 Visits: 7851
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
  • 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

  • dcdanoland
    dcdanoland
    SSC Rookie
    SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

    Group: General Forum Members
    Points: 49 Visits: 166
    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
    mister.magoo
    SSCrazy
    SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

    Group: General Forum Members
    Points: 2653 Visits: 7851
    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
  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search