2.7w+
社区成员
- 全部
如何在.Net中调用DTS包并返回成功与否的结果。
小弟通过COM等方式在C#中可以调用DTS包，但是Execute()方法是void的，我怎么才能知道这个DTS包执行是否成功呢？
曾在一些帮助文档中查到下面的资料：
------------------------------
执行程序包
并非一定要使用所提供的工具来执行 DTS 软件包，可以用编程方式执行 DTS 软件包并通过 COM 事件显示进度，并允许构建嵌入的或自定义的 DTS 执行环境。
-------------------------------
但是小弟不知道怎么用这个COM，有没有大虾用过这个，能提供点代码样例么?
曾在一些帮助文档中查到下面的资料：
------------------------------
执行程序包
并非一定要使用所提供的工具来执行 DTS 软件包，可以用编程方式执行 DTS 软件包并通过 COM 事件显示进度，并允许构建嵌入的或自定义的 DTS 执行环境。
-------------------------------
但是小弟不知道怎么用这个COM，有没有大虾用过这个，能提供点代码样例么?
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
bugchen888 2005-07-21
Handling DTS Errors in Visual Basic
You need to take the steps described below when handling errors in Data Transformation Services (DTS) applications implemented in Microsoft® Visual Basic®.
DTS applications typically consist of two phases:
In the first phase, the applications create DTS objects, set their properties, and add them to collections of parent objects.
Errors that occur during the object creation/property definition phase can be handled by a typical Visual Basic error handler.
In the second phase, the Execute method of the Package2 object is invoked.
Errors that occur during the Execute will not be propagated back to the caller unless the FailOnError property of the Package2 object is set to TRUE.
When FailOnError is TRUE, the description of the returned error will often tell you only that the package failed because a (named) step failed. To determine why a step failed, the GetExecutionErrorInfo method of the Step object will return the properties of a Visual Basic error object that describe the error.
Troubleshooting Package Execution
To determine the step(s) that raised errors, the ExecutionStatus property of the Step object should have the value DTSStepExecStat_Completed (in enum DTS.DTSStepExecStatus) and the ExecutionResult property should have the value DTSStepExecResult_Failure (in enum DTS.DTSStepExecResult). If FailOnError is TRUE, there will only be one such step. If not, there may be multiple failed steps, depending on the package workflow. The error handler should iterate through all the objects in the Steps collection and not stop when it finds an error.
Error Handler ExampleThe 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 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
Error Message ExampleThe 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.)
You need to take the steps described below when handling errors in Data Transformation Services (DTS) applications implemented in Microsoft® Visual Basic®.
DTS applications typically consist of two phases:
In the first phase, the applications create DTS objects, set their properties, and add them to collections of parent objects.
Errors that occur during the object creation/property definition phase can be handled by a typical Visual Basic error handler.
In the second phase, the Execute method of the Package2 object is invoked.
Errors that occur during the Execute will not be propagated back to the caller unless the FailOnError property of the Package2 object is set to TRUE.
When FailOnError is TRUE, the description of the returned error will often tell you only that the package failed because a (named) step failed. To determine why a step failed, the GetExecutionErrorInfo method of the Step object will return the properties of a Visual Basic error object that describe the error.
Troubleshooting Package Execution
To determine the step(s) that raised errors, the ExecutionStatus property of the Step object should have the value DTSStepExecStat_Completed (in enum DTS.DTSStepExecStatus) and the ExecutionResult property should have the value DTSStepExecResult_Failure (in enum DTS.DTSStepExecResult). If FailOnError is TRUE, there will only be one such step. If not, there may be multiple failed steps, depending on the package workflow. The error handler should iterate through all the objects in the Steps collection and not stop when it finds an error.
Error Handler ExampleThe 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 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
Error Message ExampleThe 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.)
poormsteven 2005-07-21
帮你顶一下
poormsteven 2005-07-20
关于com的用法，关注一下
poormsteven 2005-07-20
偶是用一个笨办法，exec master..xp_cmdshell 'dtsrun /s server /U User /P Password /E /N DTSName'返回的是一个记录集，然后判断里面的内容即可
