DTS and error trapping in VB

DTS and error trapping in VB

Post by Paul Baue » Sat, 23 Feb 2002 02:56:34



I have a VB application that has a DTS package in it. It seems that the code
assembles the package and then fires a package.execute method. Is there a
way to tell within my VB app if an error occurred during the execution of
that package. It does not appear that the execute method returns any value
so that I could test to see if the execution failed or succeeded. Thanks for
your help.

Paul Bauer

 
 
 

DTS and error trapping in VB

Post by Darren Gree » Sat, 23 Feb 2002 06:05:37




Quote:>I have a VB application that has a DTS package in it. It seems that the code
>assembles the package and then fires a package.execute method. Is there a
>way to tell within my VB app if an error occurred during the execution of
>that package. It does not appear that the execute method returns any value
>so that I could test to see if the execution failed or succeeded. Thanks for
>your help.

Two main methods, checking the step's ExecutionResult and using events.

Execute a package from Visual Basic (VB)
http://www.sqldts.com/article.asp?nav=1,6,208,7

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

DTS and error trapping in VB

Post by Paul Baue » Sat, 23 Feb 2002 07:17:42


Thanks Darren. I will give this a try.

Paul





Quote:>I have a VB application that has a DTS package in it. It seems that the
code
>assembles the package and then fires a package.execute method. Is there a
>way to tell within my VB app if an error occurred during the execution of
>that package. It does not appear that the execute method returns any value
>so that I could test to see if the execution failed or succeeded. Thanks
for
>your help.

Two main methods, checking the step's ExecutionResult and using events.

Execute a package from Visual Basic (VB)
http://www.sqldts.com/article.asp?nav=1,6,208,7

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

DTS and error trapping in VB

Post by Apri » Thu, 21 Mar 2002 00:34:21


I got the following code from the help file and it worked
for me.  I wrote the error message to a file instead of
using a message box though.

Private Sub RunDTSPackage( )
    Dim objPackage            As New DTS.Package
    . . .
    On Error GoTo PackageError
    . . .
    objPackage.FailOnError = True
    objPackage.Execute
    Exit Sub

PackageError:
    Dim sMsg    As String
    sMsg = "Package failed, error: " & sErrorNumConv
(Err.Number) & _
        vbCrLf & Err.Description & vbCrLf &
sAccumStepErrors(objPackage)
    MsgBox sMsg, vbExclamation, objPackage.Name
    Exit Function
End Sub

Private Function sAccumStepErrors( _
            ByVal objPackage As DTS.Package) As String
'Accumulate the step error info into the error message.
    Dim oStep       As DTS.Step
    Dim sMessage    As String
    Dim lErrNum     As Long
    Dim sDescr      As String
    Dim sSource     As String

    'Look for steps that completed and failed.
    For Each oStep In objPackage.Steps
        If oStep.ExecutionStatus =
DTSStepExecStat_Completed Then
            If oStep.ExecutionResult =
DTSStepExecResult_Failure Then

                'Get the step error information and append
it to the message.
                oStep.GetExecutionErrorInfo lErrNum,
sSource, sDescr
                sMessage = sMessage & vbCrLf & _
                        "Step " & oStep.Name & " failed,
error: " & _
                        sErrorNumConv(lErrNum) & vbCrLf &
sDescr & vbCrLf
            End If
        End If
    Next
    sAccumStepErrors = sMessage
End Function

Private Function sErrorNumConv(ByVal lErrNum As Long) As
String
'Convert the error number into readable forms, both
hexadecimal and decimal for the low-order word.

    If lErrNum < 65536 And lErrNum > -65536 Then
        sErrorNumConv = "x" & Hex(lErrNum) & ",  " & CStr
(lErrNum)
    Else
        sErrorNumConv = "x" & Hex(lErrNum) & ",  x" & _
                Hex(lErrNum And -65536) & " + " & CStr
(lErrNum And 65535)
    End If
End Function

Quote:>-----Original Message-----
>I have a VB application that has a DTS package in it. It
seems that the code
>assembles the package and then fires a package.execute
method. Is there a
>way to tell within my VB app if an error occurred during
the execution of
>that package. It does not appear that the execute method
returns any value
>so that I could test to see if the execution failed or

succeeded. Thanks for
>your help.

>Paul Bauer

>.

 
 
 

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

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

2. Fancy ADD COLUMN

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

4. date conversions (dBase)

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

6. ISQL in a DLL?

7. ado errors not trapped by vb error object???

8. Deadlock errors & vb error trapping

9. DTS Trap Error

10. Programming DTS Application - Error Trapping

11. DTS Error Trapping

12. DTS with stored procedures / error trapping