Returning package status to Access
-
I'm running a DTS package from Access with code along the lines of the below, which works fine. My issue is with the code which checks the ExecutionResult property, which returns a fail for any tasks on an OnFailure workflow which (ideally) never get executed. How can I return the execution status of the package, rather than that of the individual steps?
(Apologies for the cross posting - I put this in Microsoft Access but haven't had any response)
Set oPKG = New DTS.Package
Dim sServer As String, sUsername As String, sPassword As String
Dim sMessage As String
Dim lErr As Long, sSource As String, sDesc As String
PackageName="MyPackage"
sServer = "Server1"
Fail = 0
oPKG.LoadFromSQLServer sServer, , , DTSSQLStgFlag_UseTrustedConnection, , , , PackageName
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
oPKG.Execute
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Fail = Fail + 1
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next
Thanks
--
Scott -
Lets try and get past why you would store the package in SQL Server, but not execute it in SQL Server. I mean that way you'd be able to take advantage of all the logging and error reporting capabilities built into the product.
The package status is governed by the Execution result of the steps. You can have a step fail, but not fail the entire package and vice-versa. Maybe you can try checking the ExecutionStatus to verifiy the step was executed.
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply