SQL 2005 HTML Job Report

'**********************************************************************************************************' Name: SQL Server Job Report (For SQL 2005 SSIS)' Script Language: VBScript'' Description:' This script will generate an HTML report of the last run status for all jobs on a specified' SQL Server instance. This report can be emailed to specified addresses if required, or' the generated HTML file may be published to a web site or distributed with any other file' distribution mechanisms.' The report is broken down into three sections: a summary report of the number of jobs' which succeeded, failed, were cancelled or have an UNKNOWN status; a list of jobs by' status; a detailed report on the last recorded run of each job. Each section in the report' is hyper-linked to allow easy reference and navigation between sections.'' ------------------------------------------------------------------------------------' Modifications:' Date: Author: Notes: ' 7/3/08 Ray Sotkiewicz Converted package to SQL 2005 ' ------------------------------------------------------------------------------------' Addendum:' Be sure these variables exist as ReadOnlyVariables in the Script Task Editor:' UID, PWD,TARGET_SERVER, WIN_SECURITY,EMAIL_REPORT, REPORT_PATH, KEEP_REPORT_FILE' ------------------------------------------------------------------------------------
' Package Global Variables:' This script requires a number of Global Variables to be created in any package ' that it is included as part of.' Variable NameReq'd/OptDescription' ----------------------------------------------------------------------------------------------------------------------------------------' TARGET_SERVERRequiredSpecifies the SQL Server instance for which the'job report will be compiled.' ADDRESSESOptionalContains a list of email addresses to which the'report will be sent. Addresses must be separated'by semi-colons, and the list must be terminated'with a semi-colon.' WIN_SECURITYRequiredSpecifies whether to use Windows Integrated'Security when connecting to the specified SQL'Server instance. Valid values are "YES" and'"NO".' UIDOptionalOnly required if Windows Integrated Security is'not being used. Specifies a valid SQL Server'user ID.' PWDOptionalOnly required if Windows Integrated Security is'not being used. Specifies the password for the'SQL Server user ID.' EMAIL_REPORTRequiredSpecifies if the job report is to be emailed. Valid'values are "YES" and "NO".' REPORT_PATHRequiredSpecifies the file path where the job report will'be written to.' KEEP_REPORT_FILERequiredSpecifies if the Report file is to be retained when'the report is generated. Valid values are "YES"'and "NO".''**********************************************************************************************************
Option Strict Off
Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.SqlServer.Server
Imports System.Data.Sqlclient
Imports Microsoft.Win32
Public Class ScriptMain
' The execution engine calls this method when the task executes.' To access the object model, use the Dts object. Connections, variables, events,' and logging features are available as static members of the Dts class.' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.' ' To open Code and Text Editor Help, press F1.' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main() Dim JobsFail, JobsSucc, JobsCanc, JobsDsbl, JobsTotl, JobsUnk, iChar As Integer Dim TextFail, TextSucc, TextCanc, TextDsbl, TextUnk As String Dim TitleFail, TitleSucc, TitleCanc, TitleDsbl, TitleUnk As String Dim MailSubject, MailText, JobInfo, AddrList, Addr, [Char] As String Dim ErrMsg, ErrLineNo, LineCount, ErrLine As Integer Dim JobName As String
 ' Declare ADO object variables Dim adoConn, rsJob, rsJobHistory, rsJobStep As Object Dim strJobSQL, strJobHistorySQL, strJobStepSQL As String
 ' Declare FileSystemObject variables Dim oFSO, OutFile, ReadFile As Object Dim TempFail, TempSucc, TempCanc, TempUnk As String
 ' Declare Outlook object variables Dim olApp, olMail, olNameSpace As Object
 ' Create ADO objects adoConn = CreateObject("ADODB.Connection") rsJob = CreateObject("ADODB.RecordSet") rsJobHistory = CreateObject("ADODB.RecordSet") rsJobStep = CreateObject("ADODB.Recordset")
 ' Open ADO connection using windows security If Dts.Variables("WIN_SECURITY").Value Is "YES" Then adoConn.Open = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=" & Dts.Variables("TARGET_SERVER").Value & ";Initial Catalog = MSDB" Else adoConn.Open = "Provider=SQLOLEDB;UID=" & Dts.Variables("UID").Value & ";PWD=" & Dts.Variables("PWD").Value & ";Data Source=" & Dts.Variables("TARGET_SERVER").Value & ";Initial Catalog = MSDB" End If
 ' Set query test for jobs strJobSQL = "EXEC msdb.dbo.sp_help_job" ' Open ADO record set rsJob.Open(strJobSQL, adoConn)
 ' Create output file oFSO = CreateObject("Scripting.FileSystemObject") OutFile = oFSO.CreateTextFile(Dts.Variables("REPORT_PATH").Value & Dts.Variables("TARGET_SERVER").Value & ".html", True) OutFile.WriteLine("<HTML>") OutFile.WriteLine("<HEAD>") OutFile.WriteLine("<TITLE>Job Status Report for " & Dts.Variables("TARGET_SERVER").Value & " on " & Date.Now & "</TITLE>") OutFile.WriteLine("</HEAD>") OutFile.WriteLine("<BODY>") OutFile.WriteLine("<FONT SIZE=5 COLOR=""DARKBLUE""><B><U>Job Status Report for " & Dts.Variables("TARGET_SERVER").Value & " on " & Date.Now & "</U></B></FONT>") OutFile.WriteLine("<BR><BR><HR>")
 ' 'Create Outlook objects ' If Dts.Variables("EMAIL_REPORT").Value = "YES" Then ' olApp = CreateObject("Outlook.Application") ' olNameSpace = olApp.GetNameSpace("MAPI") ' olNameSpace.Logon() ' olMail = olApp.CreateItem(0) ' End If
 ' Loop through all record for summary report
 JobsFail = 0 TempFail = "" JobsSucc = 0 TempSucc = "" JobsCanc = 0 TempCanc = "" JobsTotl = 0 JobsUnk = 0 TempUnk = ""
 OutFile.WriteLine("<FONT COLOR=""DARKRED""><B><U><A NAME=""SummaryTitle"">SUMMARY REPORT OF JOBS</U></B></FONT>") OutFile.WriteLine("<P>") rsJob.MoveFirst() JobName = "" While Not rsJob.Eof JobsTotl = JobsTotl + 1 If rsJob.Fields.Item(21).value = 1 Then JobsSucc = JobsSucc + 1
 'JobDateTime(RTrim(rsJob.Fields.Item(19).ToString), RTrim(rsJob.Fields.Item(20)).ToString).ToString &
 TempSucc = TempSucc & "<TR><TD><A HREF=""#" & rsJob.Fields.Item(0).value.ToString & """>" & rsJob.Fields.Item(2).value.ToString & "</A></TD>" If rsJob.Fields.Item(3).value = 0 Then TempSucc = TempSucc & "<TD>NO</TD>" Else TempSucc = TempSucc & "<TD>YES</TD>" End If TempSucc = TempSucc & "<TD>" & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) & "</TD></TR>" & vbCrLf ElseIf rsJob.Fields.Item(21).value = 3 Then JobsCanc = JobsCanc + 1 TempCanc = TempCanc & "<TR><TD><A HREF=""#" & rsJob.Fields.Item(0).value.ToString & """>" & rsJob.Fields.Item(2).value.ToString & "</A></TD>" If rsJob.Fields.Item(3).value = 0 Then TempCanc = TempCanc & "<TD>NO</TD>" Else TempCanc = TempCanc & "<TD>YES</TD>" End If TempCanc = TempCanc & "<TD>" & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) & "</TD></TR>" & vbCrLf
 ElseIf rsJob.Fields.Item(21).value = 5 Then JobsUnk = JobsUnk + 1 TempUnk = TempUnk & "<TR><TD><A HREF=""#" & rsJob.Fields.Item(0).value.ToString & """>" & rsJob.Fields.Item(2).value.ToString & "</A></TD>" If rsJob.Fields.Item(3).value = 0 Then TempUnk = TempUnk & "<TD>NO</TD>" Else TempUnk = TempUnk & "<TD>YES</TD>" End If TempUnk = TempUnk & "<TD>" & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) & "</TD></TR>" & vbCrLf
 Else JobsFail = JobsFail + 1 TempFail = TempFail & "<TR><TD><A HREF=""#" & rsJob.Fields.Item(0).value.ToString & """>" & rsJob.Fields.Item(2).value.ToString & "</A></TD>" If rsJob.Fields.Item(3).value = 0 Then TempFail = TempFail & "<TD>NO</TD>" Else TempFail = TempFail & "<TD>YES</TD>" End If TempFail = TempFail & "<TD>" & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) & "</TD></TR>" & vbCrLf End If rsJob.MoveNext() End While
 ' Create summary report of job statistics OutFile.WriteLine("<TABLE BORDER=1 WIDTH=250>") OutFile.WriteLine("<TR><TD><A HREF=""#JobsSucc"">Successful Jobs</A></TD><TD>" & JobsSucc & "</TD></TR>") OutFile.WriteLine("<TR><TD><A HREF=""#JobsFail"">Failed Jobs</A></TD><TD>" & JobsFail & "</TD></TR>") OutFile.WriteLine("<TR><TD><A HREF=""#JobsCanc"">Cancelled Jobs</A></TD><TD>" & JobsCanc & "</TD></TR>") OutFile.WriteLine("<TR><TD><A HREF=""#JobsUnk"">Job Status UNKNOWN</A></TD><TD>" & JobsUnk & "</TD></TR>") OutFile.WriteLine("<TR></TR><TR></TR><TR></TR><TR></TR>") OutFile.WriteLine("<TR><TD><B>Total Jobs</TD><TD><B>" & JobsTotl & "</TD></TR>") OutFile.WriteLine("</TABLE>") OutFile.WriteLine("<BR><BR>")
 ' Create summary reports by job status TempSucc = "<TR BGCOLOR=""GREEN""><TD><B><FONT COLOR=""WHITE""><A NAME=""JobsSucc"">Jobs Succeeded : " & JobsSucc & "</TD><TD WIDTH=50><FONT COLOR=""WHITE""><B>Enbl</TD><TD WIDTH=150><FONT COLOR=""WHITE""><B>Last Run Time</TD></TR>" & vbCrLf & TempSucc TempSucc = "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf & TempSucc TempSucc = TempSucc & "</TABLE>" & vbCrLf TempSucc = TempSucc & "<A HREF=""#SummaryTitle""><FONT SIZE=1>Back To Top</FONT></A>" & vbCrLf TempSucc = TempSucc & "<BR><BR>" & vbCrLf
 TempFail = "<TR BGCOLOR=""YELLOW""><TD><B><A NAME=""JobsFail"">Jobs Failed : " & JobsFail & "</TD><TD WIDTH=50><B>Enbl</TD><TD WIDTH=150><B>Last Run Time</TD></TR>" & vbCrLf & TempFail TempFail = "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf & TempFail TempFail = TempFail & "</TABLE>" & vbCrLf TempFail = TempFail & "<A HREF=""#SummaryTitle""><FONT SIZE=1>Back To Top</FONT></A>" & vbCrLf TempFail = TempFail & "<BR><BR>" & vbCrLf
 TempCanc = "<TR BGCOLOR=""YELLOW""><TD><B><A NAME=""JobsCanc"">Jobs Cancelled : " & JobsCanc & "</TD><TD WIDTH=50><B>Enbl</TD><TD WIDTH=150><B>Last Run Time</TD></TR>" & vbCrLf & TempCanc TempCanc = "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf & TempCanc TempCanc = TempCanc & "</TABLE>" & vbCrLf TempCanc = TempCanc & "<A HREF=""#SummaryTitle""><FONT SIZE=1>Back To Top</FONT></A>" & vbCrLf TempCanc = TempCanc & "<BR><BR>" & vbCrLf
 TempUnk = "<TR BGCOLOR=""YELLOW""><TD><B><A NAME=""JobsUnk"">Job Status UNKNOWN : " & JobsUnk & "</TD><TD WIDTH=50><B>Enbl</TD><TD WIDTH=150><B>Last Run Time</TD></TR>" & vbCrLf & TempUnk TempUnk = "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf & TempUnk TempUnk = TempUnk & "</TABLE>" & vbCrLf TempUnk = TempUnk & "<A HREF=""#SummaryTitle""><FONT SIZE=1>Back To Top</FONT></A>" & vbCrLf TempUnk = TempUnk & "<BR><BR>" & vbCrLf
 ' Add reports to mail - Successful job list not currently added OutFile.Write(TempFail) OutFile.Write(TempCanc) OutFile.Write(TempUnk) OutFile.Write(TempSucc) OutFile.WriteLine("<HR>")
 ' Loop through all records for detailed report OutFile.WriteLine("<FONT COLOR=""DARKRED""><B><U><A NAME=""DetailTitle"">DETAIL REPORT OF JOBS</U></B></FONT>") OutFile.WriteLine("<P>") rsJob.MoveFirst() JobName = "" While Not rsJob.Eof ' Write Heading for current job OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR><TD BGCOLOR=""NAVY""><B><FONT COLOR=""WHITE""><A NAME=""" & rsJob.Fields.Item(0).value.ToString & """>" & rsJob.Fields.Item(2).value.ToString & "</TD></TR>") OutFile.WriteLine("</TABLE>") OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR><TD WIDTH=100>Job Status</TD><TD>" & JobStatus(rsJob.Fields.Item(21).value).ToString & "</TD></TR>") If rsJob.Fields.Item(24).value = 0 Then OutFile.WriteLine("<TR><TD>Next Run Time</TD><TD><I>Next Run Time Not Set</TD></TR>") Else OutFile.WriteLine("<TR><TD>Next Run Time</TD><TD>" & JobDateTime(RTrim(rsJob.Fields.Item(22).value.ToString), RTrim(rsJob.Fields.Item(23).value.ToString)) & "</TD></TR>") End If OutFile.WriteLine("</TABLE>") ' Open Job History for Current Job If rsJobStep.State <> 0 Then rsJobStep.Close() strJobStepSQL = "EXEC msdb.dbo.sp_help_jobstep @job_id='" & rsJob.Fields.Item(0).value.ToString & "'" rsJobStep.Open(strJobStepSQL, adoConn) ' Write last run outcome OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR><TD><B>Last Run Outcome</TD></TR>") If rsJob.Fields.Item(19).value <> 0 Then
 strJobHistorySQL = "EXEC msdb.dbo.sp_help_jobhistory @job_id='" & rsJob.Fields.Item(0).value.ToString & "', @step_id = 0, @mode='FULL', @start_run_date=" & rsJob.Fields.Item(19).value.ToString & ", @start_run_time=" & rsJob.Fields.Item(20).value.ToString
 rsJobHistory.Open(strJobHistorySQL, adoConn) If rsJobHistory.EOF And rsJobHistory.BOF Then OutFile.WriteLine("<TR><TD><I>No History Found For this Job</TD></TR>") Else OutFile.WriteLine("<TR><TD>" & rsJobHistory.Fields.Item(7).value.ToString & "</TD></TR>") End If rsJobHistory.Close() Else OutFile.WriteLine("<TR><TD><I>No History Found For this Job</TD></TR>") End If OutFile.WriteLine("</TABLE>") ' Write Job Step Report While Not rsJobStep.EOF OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR><TD><B>Step #" & rsJobStep.Fields.Item(0).value.ToString & " - " & rsJobStep.Fields.Item(1).value.ToString & "</TD></TR>") OutFile.WriteLine("</TABLE>") OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR><TD WIDTH=100>Last Run Time</TD><TD>" & JobDateTime(RTrim(rsJobStep.Fields.Item(20).value.ToString), RTrim(rsJobStep.Fields.Item(21).value.ToString)) & "</TD></TR>") OutFile.WriteLine("<TR><TD>Command Type</TD><TD>" & rsJobStep.Fields.Item(2).value.ToString & "</TD></TR>") OutFile.WriteLine("<TR><TD>Command</TD><TD>" & Replace(rsJobStep.Fields.Item(3).value.ToString, vbCrLf, "<BR>") & "</TD></TR>") If rsJob.Fields.Item(19).value <> 0 Then strJobHistorySQL = "EXEC msdb.dbo.sp_help_jobhistory @job_id='" & rsJob.Fields.Item(0).value.ToString & "', @step_id = " & rsJobStep.Fields.Item(0).value.ToString & ", @mode='FULL', @start_run_date=" & rsJob.Fields.Item(19).value.ToString & ", @start_run_time=" & rsJob.Fields.Item(20).value.ToString rsJobHistory.Open(strJobHistorySQL, adoConn) If Not (rsJobHistory.EOF And rsJobHistory.BOF) Then OutFile.WriteLine("<TR><TD>Message</TD><TD>" & Replace(rsJobHistory.Fields.Item(7).value.ToString, vbCrLf, "<BR>") & "</TD></TR>") End If rsJobHistory.Close() End If OutFile.WriteLine("</TABLE>") rsJobStep.MoveNext() End While rsJob.MoveNext() ' Close off Report OutFile.WriteLine("<A HREF=""#SummaryTitle""><FONT SIZE=1>Back To Summary Report</FONT></A>") OutFile.WriteLine("<P>") End While
 If JobsFail = 0 Then MailSubject = "Job Status : " & Dts.Variables("TARGET_SERVER").Value & " : No Failures" Else MailSubject = "Job Status : " & Dts.Variables("TARGET_SERVER").Value & " : " & JobsFail & " Failures" End If
 ' Close report file OutFile.WriteLine("</BODY>") OutFile.WriteLine("</HTML>") OutFile.Close()
 ReadFile = oFSO.OpenTextFile(Dts.Variables("REPORT_PATH").Value & Dts.Variables("TARGET_SERVER").Value & ".html", 1)
 'Determine if report is to be emailed If Dts.Variables("EMAIL_REPORT").Value = "YES" Then ' Set email properties AddrList = Dts.Variables("ADDRESSES").Value Addr = "" For iChar = 1 To Len(AddrList) - 1 If Mid(AddrList, iChar, 1) = ";" Then olMail.Recipients.Add(Addr) Addr = "" Else Addr = Addr & Mid(AddrList, iChar, 1) End If Next olMail.Recipients.Add(Addr) olMail.Recipients.ResolveAll() olMail.Subject = MailSubject olMail.HTMLBody = ReadFile.ReadAll olMail.Send() End If
 ' Clean Up ReadFile.Close() If Dts.Variables("KEEP_REPORT_FILE").Value = "NO" Then oFSO.DeleteFile(Dts.Variables("REPORT_PATH").Value, True) End If oFSO = Nothing olMail = Nothing olApp = Nothing
 Dts.TaskResult = Dts.Results.Success
 End Sub
 '********************************************************************************************************** ' Function:JobDateTime ' Description:Accepts unformatted data and time parameters and returns formatted 'datetime string as "DD Mon YYYY HH:MM:SS". '********************************************************************************************************** Private Function JobDateTime(ByVal InDate As String, ByVal InTime As String) As String
 Dim strDate As String, strTime As String, dtDateTime As Date
 strDate = "" strTime = ""
 If InDate = "0" Then strDate = "Unknown" Else 'strDate = Right(InDate, 2) & " " & MonthName(CInt(Mid(InDate, 5, 2)), True) & " " & Left(InDate, 4) strDate = Right(InDate, 2) & "/" & Mid(InDate, 5, 2) & "/" & Left(InDate, 4) End If
 If InTime = "0" Then strTime = "" Else If Len(InTime) = 5 Then InTime = "0" & InTime strTime = Left(InTime, 2) & ":" & Mid(InTime, 3, 2) & ":" & Right(InTime, 2) End If
 JobDateTime = strDate & " " & strTime
 End Function
 '********************************************************************************************************** ' Function:JobStatus ' Description:Accepts numeric status code and returns string description. '********************************************************************************************************** Private Function JobStatus(ByVal InStatus As Integer) As String
 If InStatus = 1 Then JobStatus = "SUCCESS" ElseIf InStatus = 3 Then JobStatus = "CANCELLED" ElseIf InStatus = 5 Then JobStatus = "UNKNOWN" Else JobStatus = "FAILED" End If
 End Function
End Class