The following code example is a typical error handler that could be used while a package is being developed, and FailOnError is set to TRUE. If failing the package on the first error is undesirable, the sAccumStepErrors function could still be used, but it would need to be called following a normal return from objPackage.Execute, as well as from the error handler.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 SubPrivate 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 FunctionPrivate 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 FunctionThe following code example is the message generated by the above handler when a package with a connection that references a non-existent database is run:Package failed, error: x80040428, x80040000 + 1064 Package failed because Step 'ParallelDPStep' failed.Step ParallelDPStep failed, error: x80074005, x80070000 + 16389 Data provider could not be initialized. (Microsoft OLE DB Provider for SQL Server (80004005): Cannot open database requested in login 'DTSFest'. Login fails.)
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 SubPrivate 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 FunctionPrivate 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 FunctionThe following code example is the message generated by the above handler when a package with a connection that references a non-existent database is run:Package failed, error: x80040428, x80040000 + 1064
Package failed because Step 'ParallelDPStep' failed.Step ParallelDPStep failed, error: x80074005, x80070000 + 16389
Data provider could not be initialized. (Microsoft OLE DB Provider
for SQL Server (80004005): Cannot open database requested in login
'DTSFest'. Login fails.)
能不能ping通该数据服务器。