Error Trapping - Can you trap a DTS Package error using SQLDMO running a job

by mike » Mon, 22 Sep 2003 03:46:11

I'm running a DTS pacakge by scheduling it through a job
in SQL DMO (I am doing it through DMO vs. DTS Object in
order to run the package on the server from a client pc).
I am having trouble finding info on how to retreive the
error messages if any of the packages fail. I know I can
make an error log and look at that text file but for the
application purposes it would be a lot better to pull it
in through DMO, any suggestions on how to find the error
message? I've looked a little into the Alert object but
don't fully understand how that might or might not help
me. Thanks in advance,
Mike

Top

1. Error Trapping: MS Access errors not trapped in VB code

I have an error trap in my VB 5.0 code that doesn't work when an error
is coming from and MS Access db. These are errors that occur when
there is a primary key violation or a referential integrity violation.
I have done similar error trapping in the past and it worked fine.
The only difference in code is that I was using a data control in the
code that worked and now I'm working directly with the recordsets.

The following is the code sample. The error occus on rsPallet.Update:

On Error GoTo ErrHandler

rsPallet.AddNew
rsPallet![StockNum] = txtPaperType.Text
rsPallet![LoadTicket] = txtLoadNum.Text
rsPallet![LinYds] = txtTotYards.Text
rsPallet![DateRec] = txtTruckDate.Text
rsPallet![RollCount] = txtNumRolls.Text
rsPallet![UserId] = gUserID

ErrHandler:
Dim errorNumber
errorNumber = Err.Number
Select Case errorNumber
Case 3022 'primary key violation - load number is not unique for
the truck date given
message = "Load number " & txtLoadNum.Text & " has been used
on " & txtTruckDate.Text _
& ". Would you like me to assign a unique load
number?"
answer = MsgBox(message, vbYesNo, "Primary Key Violation")
If answer = vbNo Then
txtStockNum.SetFocus
txtStockNum.SelStart = 0
txtStockNum.SelLength = Len(txtStockNum.Text)
Else
loadNumber = rsLoadNum![UniqueNum]
txtStockNum.Text = loadNumber
rsLoadNum![UniqueNum] = (loadNumber + 1)
End If
Case 3201 'invalid paper type, number given doesn't exit in
Paper table
message = "The stock number " & txtStockNum.Text & " does not
exist." & _
" Please enter a valid stock number."
MsgBox message, vbExclamation, "Invalid Stock Number"
Case Else
message = "The following error has occured: " & Err.Number &
", " & Err.Description