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

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

Post 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
 
 
 

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

    End Select

Has anyone else encounter this problem or know the solution?

Thanks.

Sharon Stokesbary

2. codepage 1252-1254 problem

3. Trapping DTS package errors

4. RDA Pull in Win2K - is it possible?

5. Trap error raised from a task in DTS Package

6. NY-ORACLE DEVELOPER

7. Trapping SQL error in VB before ADO raises run-time error

8. Link Access Table

9. SQLDMO Error Trapping

10. Package run correctly in dts designer, but run error when schedual this package

11. DTS Package Failed When Running As A Schedule Job - Error -2147024893

12. Can someone show me how to trap an run-time error

13. sql job error trapping