SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enterprise SQL Job Activity Reporting


Enterprise SQL Job Activity Reporting

Author
Message
Craig Benson
Craig Benson
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 359
Comments posted to this topic are about the item Enterprise SQL Job Activity Reporting
dave-dj
dave-dj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1149
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)
Craig Benson
Craig Benson
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 359
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!
Duude
Duude
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 236
Hi!

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

Thanks!!
Andy Warren
Andy Warren
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Moderators
Points: 12137 Visits: 2730
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
Craig Benson
Craig Benson
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 359
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!
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 1095
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
Data Philosopher, Enterprise Data & Analytics
Duude
Duude
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 236
Thank you Craig.

The only problem that I see in your good script, is to enable 'Ad Hoc Distributed Queries'.
Craig Benson
Craig Benson
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 359
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^)
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2874 Visits: 4674
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.
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