DTS package finds all failed jobs... quick question??

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  August 9, 2006 at 7:21 am

  #60684

  this dts package is a vb script which will send you a list

  of all failed jobs across your servers. it produces an

  html file for you.

  it's really just a vb script which you incorporate into dts.

  i'm having some trouble with it, and I was wondering if it's

  possible to convert this over to a job which will produce the

  same html file some where in a folder.

  here is the script.

  thoughts?

  This is the script. The XML file follows.

  '**********************************************************************

  ' Visual Basic ActiveX Script

  '************************************************************************

  Dim xmlServers

  Function Main()

  ' Read in the server list

  InitServers

  ' Get the job status and write the web page

  GetJobStatus

  Main = DTSTaskExecResult_Success

  End Function

  Sub GetJobStatus()

  Dim xmlServer

  Dim ServerName

  Dim ConnectionString

  Dim cn

  Dim rs

  Dim SQL

  Dim JobName

  Dim ErrorMessage

  Dim RunDate

  Dim RunTime

  Dim OK

  Dim fso

  Dim ts

  Set fso = CreateObject("Scripting.FileSystemObject")

  Set ts = fso.OpenTextFile("\\DBAWEB01\SQL$\SQLJobStatus.htm", 2,-1)

  Set cn = CreateObject("ADODB.Connection")

  ' If this is monday, the include the whole weekend

  RunDate = Now()

  if DatePart( "w", RunDate ) = 2 then

  RunDate = DateAdd("d",-3,RunDate)

  else

  RunDate = DateAdd("d",-1,RunDate)

  end if

  ts.WriteLine "" & _

  "" & _

  ""

  ts.WriteLine "" & _

  "

  SQL Server Job Failures

  " & _

  "

  " & FormatDateTime(Now(),vbShortDate) & "

  " & _

  "

  from " & FormatDateTime(RunDate,vbShortDate) & " to date

  " & _

  "

  "

  RunDate = Year(RunDate) * 10000 + Month(RunDate) * 100 + Day(RunDate)

  SQL = "select j.name, jh.message, jh.run_date, jh.run_time" & _

  " from sysjobs j" & _

  " inner join sysjobhistory jh" & _

  " on j.job_id = jh.job_id" & _

  " and jh.step_id = 0" & _

  " and run_status = 0" & _

  " and run_date >= " & RunDate & _

  " where j.enabled = 1" & _

  " order by j.name, jh.run_date, jh.run_time "

  For Each xmlServer In xmlServers.selectNodes("//Servers/Server")

  ServerName = xmlServer.getAttribute("Name")

  ConnectionString = "provider=sqloledb;server=" & ServerName & ";database=msdb;"

  If xmlServer.getAttribute("IntegratedSecurity") = "Yes" Then

  ConnectionString = ConnectionString & "Trusted_Connection=yes"

  Else

  ConnectionString = ConnectionString & "UID=" & _

  xmlServer.getAttribute("UID") & ";PWD=" & _

  xmlServer.getAttribute("PWD")

  End If

  ts.WriteLine "

  "

  On Error Resume Next

  cn.Open ConnectionString

  If Err.Number = 0 Then

  On Error GoTo 0

  Set rs = cn.Execute(SQL)

  Do Until rs.EOF

  If Not IsNull(rs("message").Value) Then

  JobName = rs("name").Value

  ErrorMessage = rs("message").Value

  RunDate = DateSerial(rs("run_date").Value / 10000, rs("run_date").Value / 100 Mod 100, rs("run_date").Value Mod 100)

  RunTime = TimeSerial(rs("run_time").Value / 10000, rs("run_time").Value / 100 Mod 100, rs("run_time").Value Mod 100)

  ts.WriteLine "

  "

  End If

  ts.WriteLine "

  "

  rs.MoveNext

  Loop

  rs.Close

  cn.Close

  Else

  ts.WriteLine "

  "

  End If

  On Error GoTo 0

  Next

  ts.WriteLine "

  Server	Job Name	Message	Failure Date
  " & ServerName & "
  " & ServerName & "	" & JobName & _ "	" & ErrorMessage & _ "	" & FormatDateTime(RunDate,vbshortdate) & _ " " & FormatDateTime(RunTime, vbShortTime) & "
  Unable to connect:" & Err.Description & "

  "

  ts.Close

  Set ts = Nothing

  Set fso = Nothing

  End Sub

  Sub InitServers()

  set xmlServers = createObject("MSXML.DOMDocument")

  xmlServers.Load "\\DEVSQL01\dts\dba\SQLServerJobs.ini"

  End Sub

  This is the XML configuration file (SQLServerJobs.ini):

  _________________________

• barsuk

  SSChampion

  Points: 12280

  More actions

  August 10, 2006 at 8:33 pm

  #654465

  you incorporated ActiveX in DTS package. Can't you just pass it in the job as:

  DTSRUN /N"ReadFailedJobDTS" /E /SYourServer