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 9:46 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:01 PM
Points: 25, Visits: 140
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
Post #1432748
Posted Tuesday, March 19, 2013 7:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 8:34 AM
Points: 17, Visits: 152
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.
Post #1432974
Posted Wednesday, March 20, 2013 1:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Very useful article, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433046
Posted Wednesday, March 20, 2013 6:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:06 AM
Points: 54, Visits: 249
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.
Post #1433172
Posted Saturday, March 30, 2013 6:49 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:02 PM
Points: 130, Visits: 946
Does this answer your question?

http://stackoverflow.com/questions/3471821/how-to-pass-a-parameter-to-ssrs-report-from-ssis-package
Post #1437259
Posted Saturday, May 18, 2013 8:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Hey Stan,

I just looked at your article and almost all of the graphics are coming across as solid blue boxes with a little red "x" inside a smaller box at the top left of the larger. Not sure if it's just my system or if the article code actually has a problem but I thought I'd give you a heads up.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454301
Posted Saturday, May 18, 2013 9:14 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:02 PM
Points: 130, Visits: 946
I believe that the sqlservercentral.com server is having severe issues. I have been writing an article and can't modify it or add or delete resource files. I had the web administrator upload a file for me last week, but now I need to change it and can't.

I will email them again on Monday.
Post #1454304
Posted Monday, June 17, 2013 9:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:25 PM
Points: 9, Visits: 19
Thank you for the detailed instructions. Are these steps specific to 2012? Will the same steps work in SQL Server 2008 R2?
Post #1464444
Posted Tuesday, June 18, 2013 6:10 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:02 PM
Points: 130, Visits: 946
I don't have 2012 yet. I did it in 2008R2.
Post #1464602
Posted Thursday, April 10, 2014 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 2:49 PM
Points: 2, Visits: 20
Hi - I am new to SSIS .
Please correct me if I have misstated anything below:
1) The SSIS package will be connecting to the report server DB.
2) SSIS package can be placed on any server.

Please let me know if there is any way one could trigger the subscriptions using ssis without connecting to the reportserver db. Say a webservice reference in the package. What should be the data connection for the package then.?

Regards
>>>>>>>>
AJV
Post #1560580
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse