GetExecutionErrorInfo Method Fails when executing
fsanchez
I am trying to determine if any of the step in package failed. I am
executing a package from within an ActiveX script. The execution of the
package works fine; however, when I try to retrieve the error information,
the call fails indicating Type missmatch or wrong number of arguments. Here
is the sample code...Can anyone help...
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim ADOconn
Dim ADOrst
Dim oPackage
Dim oStep
Dim iErrorCode
Dim iCount
Dim sTaskName
Dim sSource
Dim sErrorDesc
Dim sHelpFileName
Dim iHelpContextID
Dim sInterfaceID
Dim sErrorMessage
Dim sErrorNumConv
Dim sProcessCategory
Dim sProcessName
Set oPackage = CreateObject("DTS.Package")
sProcessName = "3002PCKG"
oPackage.LoadFromSQLServer "(local)","","","256",,,,sProcessName
oPackage.GlobalVariables.Item("giJobID").Value =
DTSGlobalVariables("giJobID").Value
oPackage.GlobalVariables.Item("gsAS400SourceUDL").Value =
DTSGlobalVariables("gsAS400SourceUDL").Value
oPackage.GlobalVariables.Item("gsSourceUDL").Value =
DTSGlobalVariables("gsSourceUDL").Value
oPackage.GlobalVariables.Item("gsTargetUDL").Value =
DTSGlobalVariables("gsTargetUDL").Value
oPackage.Execute
'**************************************************************
' GET STATUS AND ERROR
'**************************************************************
'LOOK FOR STEPS THAT COMPLETED AND FAILED.
For iCount = 1 To oPackage.Steps.Count
If oPackage.Steps(iCount).ExecutionStatus =
DTSStepExecStat_Completed Then
If oPackage.Steps(iCount).ExecutionResult =
DTSStepExecResult_Failure Then
'**************************************************************
'GET THE STEP ERROR INFORMATION
'**************************************************************
iErrorCode = CLng(-1)
iHelpContextID = CLng(-1)
oPackage.Steps(iCount).GetExecutionErrorInfo iErrorCode, sSource,
sErrorDesc, sHelpFileName, iHelpContextID, sInterfaceID
If iErrorCode < 65536 And iErrorCode > -65536 Then
sErrorNumConv = "x" & Hex(iErrorCode) & ", " &
CStr(iErrorCode)
Else
sErrorNumConv = "x" & Hex(iErrorCode) & ", x" & _
Hex(iErrorCode And -65536) & " + " & CStr(iErrorCode
And 65535)
End If
sMessage = sMessage & vbCrLf & _
"Step " & oPackage.Steps.Name & " failed, error:
" & _
sErrorNumConv & vbCrLf & sDescr & vbCrLf
End If
End If
Next
Msgbox sMessage
oPackage.Uninitialize()
Set oPackage = Nothing
Main = DTSTaskExecResult_Success
End Function
Darren Green
of the correct type, and you only have variant available, not the required
string and int types.
You will need to write this code in a full language. Implement it as a COM
object and you can leverage it from VBScript though.
Darren Green
http://www.sqldts.com
http://www.sqlis.com
"fsanchez" <fsan...@discussions.microsoft.com> wrote in message
news:0C5106FA-6A9C-496C...@microsoft.com...
fsanchez
I very greatful for your respond. Unfortunately, I have no knowledge of
developing COM. Do you know how I can go about this or develop the COM object
and calling it from ActiveX script? Any insight greatly appreciate it.
Darren Green
"fsanchez" <fsan...@discussions.microsoft.com> wrote in message
news:E4E2E57A-73D3-4980...@microsoft.com...
fsanchez
I have Visual Basic 6.0 installed...
Darren Green
(http://www.sqldts.com/default.aspx?272)
DTS ActiveX Helper
http://www.sqldts.com/redir.aspx?45
A simple COM component which wraps the GetExecutionErrorInfo method using
variant types for returning data, compatible with ActiveX Script Tasks. Full
source code included (VB6).
Darren Green
http://www.sqldts.com
http://www.sqlis.com
"fsanchez" <fsan...@discussions.microsoft.com> wrote in message
news:E6A76EAE-491A-4DB5...@microsoft.com...
fsanchez
I am getting error while accessing the DTS ActiveX Helper webpage. The
error indicates that the web page does not exist.
fsanchez
I managed to learn how to call a COM object from within an DTS ActiveX and
return the error message. There a lot involve in creating the COM object.
However, once you create it and register the COM object. I was able to call
it as followed:
Function Main()
Dim sErrorMessage
Dim sProcessName
Dim oDTS
sProcessName = "3002PCKG"
Set oDTS = CreateObject("DWConsole.Package")
sErrorMessage = oDTS.ExecutePackage(sProcessName)
MsgBox sErrorMessage
Set oDTS = Nothing
Main = DTSTaskExecResult_Success
End Function
Here is the VB Code:
Public Function ExecutePackage(ByVal pPackageName As String) As String
Dim oPackage As DTS.Package, oStep As DTS.Step
Set oPackage = New DTS.Package
Dim sServer As String, sUsername As String, sPassword As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String
' Set Parameter Values
sServer = "(local)"
sUsername = ""
sPassword = ""
sPackageName = pPackageName
' Load Package
oPackage.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName
' Set Exec on Main Thread
For Each oStep In oPackage.Steps
oStep.ExecuteInMainThread = True
Next
' Execute
oPackage.Execute
' Get Status and Error Message
For Each oStep In oPackage.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
End If
Next
ExecutePackage = sMessage
oPackage.UnInitialize
Set oStep = Nothing
Set oPackage = Nothing
' Display Results
'MsgBox sMessage
End Function
Darren Green
In message <333B5400-62DA-41EE...@microsoft.com>,
fsanchez <fsan...@discussions.microsoft.com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
Tom P
I have downloaded that zip file, and registered the ActiveXHelper DLL as a
COM
object, but still can't get the function to work within a script. Can
you maybe tell me what I'm doing wrong? Here's a code
snippet:
dim objStepError
Set objStepError = CreateObject("DTSActiveXHelper.HelperClass")
.
.
.
For Each oStep in oPkg.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
objStepError.GetExecutionErrorInfo oStep,lErr, sSource,
sDesc,NULL,NULL,NULL
MsgBox lErr
End If
End If
Next
BTW, I registered the object by just running regsvr32 from a command line.
Thanks,
Tom
Darren Green
Function Main()
Dim oActXHelper, oPkg, oStep
Dim lErrorCode
Dim sSource
Dim sDescription
Dim sHelpFile
Dim lHelpContext
Dim sIDofInterfaceWithError
Set oActXHelper = CreateObject("DTSActiveXHelper.HelperClass")
Set oPkg = DTSGlobalVariables.Parent
For Each oStep in oPkg.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed And
oStep.ExecutionResult = DTSStepExecResult_Failure Then
oActXHelper.GetExecutionErrorInfo oStep,
lErrorCode, sSource, sDescription, sHelpFile, lHelpContext,
sIDofInterfaceWithError
MsgBox "Number: " & lErrorCode & vbCrLf &
"Source: " & sSource & vbCrLf & "Desc: " & sDescription
End If
Next
Set oStep = Nothing
Set oPkg = Nothing
Set oActXHelper = Nothing
Main = DTSTaskExecResult_Success
End Function
This works for me. You don't actually say why it doesn't work, or any
errors?
Darren
In message <0F61342C-6B81-4EFC...@microsoft.com>, Tom P
<To...@discussions.microsoft.com> writes