July 16, 2009 at 12:36 pm
I have been assigned a project to calculate the number of Jobs we run using SSIS 2005. I am very new to SSIS, about two weeks, so I'm not sure of the best way to approach this matter. Also, most of my programming skills is with REXX and Cobol, so I am not familure with SQL. I have already created an SSIS package that reads thru several logfiles a month, seperating the information by environment, PROD/MODL, etc. Next, I read these files and determine how many Jobs have run each day then write this information to a flat file. So now I'm at the point where I have to read this flat file and calculate the total number of Jobs that ran each month. Once I have calculated the total number of Jobs that ran each month, I have to calculate a failure rate, between the number of Jobs that ran, and those that failed, then somehow get this information in an email and send it to accomodate monthly reporting. Does anyone know of a way to accomplish this? Any assistance would be greatly appreciated.
July 16, 2009 at 10:13 pm
I think that is an incredibly painful way to do it..
You can read the table in msdb called sysjobhistory to get all that information I think. The one thing you may have to do is allow for more history..
That is a version dependent question:
For SQL 2000, expand Management and right click on SQL Server Agent and then click on the Job System tab.
For SQL Server 2005/2008, right click on SQL Server Agent and then from Select A Page, select History.
Adjust "Maximum job history log size (in rows)" to a number over 1000, this number should be enough to cover at least as many as the number of jobs times "Maximum job history rows per job".
CEWII
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply