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 12»»

Enterprise SQL Job Activity Reporting Expand / Collapse
Author
Message
Posted Saturday, December 5, 2009 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:42 AM
Points: 28, Visits: 240
Comments posted to this topic are about the item Enterprise SQL Job Activity Reporting
Post #829438
Posted Monday, December 7, 2009 5:09 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Hi,

This is a great idea and I've just done something similar, but more focused on producing server documentation of jobs, backup devices etc etc.

What I'd like to suggest is a slightly different alternative to acheive the same result.

I would create an SSRS report with a data source for each of your severs.
Create a report a separate report for each server to view the job history of jobs on the server.

Then using SSRS create a report which includes a sub report, point to each of the server reports.

This would remove the need for SSIS and storing data in SQL (unless of course you need a bigger than you keep for your job history..


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #829751
Posted Monday, December 7, 2009 5:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:42 AM
Points: 28, Visits: 240
I see great minds think alike! I actually set out using a pure SSRS solution, but the report generation took too long because I have so many servers, so I opted to off-load the data gathering portion using a SSIS solution and a single table that gets truncated before each run.

Nevertheless, for those out there that don't have too many servers, using a pure SSRS solution is definitely the way to go!

Post #829757
Posted Monday, December 7, 2009 5:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 2:14 AM
Points: 19, Visits: 190
Hi!

Would you extend the article with the "howto" of the "For Each Loop Container".

Thanks!!
Post #829763
Posted Monday, December 7, 2009 6:07 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:16 AM
Points: 6,784, Visits: 1,895
I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #829776
Posted Monday, December 7, 2009 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:42 AM
Points: 28, Visits: 240
I didn’t actually use the For Each Loop container, as I don’t connect to all my SQL Servers using Windows credentials.

But, if you do, here’s how you would use one:

1. In your SSIS package, you would need a VB script task that would itself have a For Each loop routine to iterate through all the SQL Servers on your network. All this routine would do is build a string list of all the SQL Server names.
2. Save the string of server names to a SSIS variable.
3. Create a For Each Loop container in the SSIS package and inside the loop iterate through the SSIS server name variable and update another SSIS variable that contains the connection string that is used by your OLE DB Source connection manager.
4. Then just follow the article steps to run the SQL Script to save the output to the designated OLE destination database.

Hope this helps!
Post #829789
Posted Monday, December 7, 2009 6:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
Andy Warren (12/7/2009)
I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.


Good point Andy,

I've built solutions like this as well, and I find architecture drives the distribution of the workload. My current environment, for example, has firewalls between Application Lifecycle tiers (Dev, Systems Integration, Production, etc.). In that case, I've opted to deploy a version of the tool to each tier individually - but if I wanted, I could pump CSV files around the enterprise and aggregate the information.

Great article Craig!

:{> Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #829790
Posted Monday, December 7, 2009 6:30 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 2:14 AM
Points: 19, Visits: 190
Thank you Craig.

The only problem that I see in your good script, is to enable 'Ad Hoc Distributed Queries'.
Post #829796
Posted Monday, December 7, 2009 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:42 AM
Points: 28, Visits: 240
Yes, there is a small security concern with having it continuously enabled. Optionally, you could enable it before the script and disable it after. Or, you could create a linked server before the script and drop it after. The cat, as they say, can be skinned a few different ways! 8^)
Post #829818
Posted Wednesday, December 9, 2009 6:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
this is pretty good

i just finished debugging a backup report system that uses SSRS to display the data and monitoring jobs is next on the list

http://www.sqlservercentral.com/articles/SQL+Server+2005/61887/

i found this article that i think i was going to base my system on, but i was going to customize it a lot. going to look at yours in more detail and probably take parts of each one.

One shortcoming I see is that in the report there is no server column and there is no filtering for failed jobs that i would like to see first instead of looking through a lot of data.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #831361
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse