ErrorDescription in Mail

• Ann-176222

  SSC Enthusiast

  Points: 184

  More actions

  April 5, 2005 at 6:00 am

  #66753

   Hi,

  I just started working with DTS and have a question.

  I just created some packages to import data from a flatfile. I want to send an e-mail each time the package fails. So far no problem, but I want to include the error-message in the mail. What's the easiest way to do this?

   

  Thanks for your help.

• philcart

  SSC-Forever

  Points: 47795

  More actions

  April 5, 2005 at 4:59 pm

  #550657

  There are various ways to do this that involve invoking COM objects and the like, but that gets a little too complicated for my liking.

  What I've found to be best is to ensure package logging is enabled for all my packages. This means that all the information I need is stored in the sysdtspackagelog and sysdtssteplog tables in the msdb database.

  Then I have on "On Failure" ActiveX Script task that grabs Package name, step name and step start. It passes this info to a generic ErrorReporter DTS package that extracts the error message and reports it via email and/or Windows Event Log and Logs messages to an SQL table.

  All that might be a bit overwhelming so here's a cut down version of the script,

  ' needs to be manually set to the step name
  Const STEP_NAME = "<<DTS Step Name>>"

  Dim oPkg, sPkgName, sStpName, dteStrtTime, sStpStrt
  Dim oSrvr, oDb, oQry, sSQL
  Dim sErrCode, sErrDesc, sMsg

  Set oPkg = DTSGlobalVariables.Parent
  sPkgName = oPkg.Name
  sStpName = oPkg.Steps(STEP_NAME).Name
  dteStrtTime = oPkg.Steps(STEP_NAME).starttime
  ' reformat datetime for SQL
  sStpStrt = Day(dteStrtTime)
  sStpStrt = sStpStrt & " " & MonthName(Month(dteStrtTime), True)
  sStpStrt = sStpStrt & " " & Year(dteStrtTime)
  sStpStrt = sStpStrt & " " & Hour(dteStrtTime)
  sStpStrt = sStpStrt & ":" & Minute(dteStrtTime)
  sStpStrt = sStpStrt & ":" & Second(dteStrtTime)

  sMsg = "DTS Package: " & sPkgName & " Step: " & sStpName & " failed. " & vbCrLf & vbCrLf
   
  Set oSrvr = CreateObject("SQLDMO.SQLServer")
  oSrvr.LoginSecure = True
  oSrvr.Connect "<<Server name>>"
  Set oDb = oSrvr.Databases("<<database name>>")
   
  ' build SQL statement that extracts error code and description
  sSQL = "SELECT errorcode, errordescription "
  sSQL = sSQL & "FROM msdb.dbo.sysdtssteplog "
  sSQL = sSQL & "WHERE stepname = '" & sStpName & "' "
  sSQL = sSQL & "AND starttime = '" & sStpStrt & "'"
   
  ' execute the query
  Set oQry = oDb.ExecuteWithResults(sSQL)

  If oQry.Rows > 0 Then
   ' build message string based on values in global variables
   ErrCode = oQry.GetColumnString(1, 1)
   ErrDesc = Replace(oQry.GetColumnString(1, 2), "'", "")
   sMsg = sMsg & "Error Code: " & ErrCode & vbCrLf & "Description: " & ErrDesc

  Else
   sMsg = sMsg & "Could not retrieve error description."
   
  End If

  ' email error
  ' etc...
  ' etc...

   

  --------------------
  Colt 45 - the original point and click interface