Hi,
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

It won't work in script because it uses by reference arguments, which must
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" <fsanchez (AT) discussions (DOT) microsoft.com> wrote

Hi Darren,
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" wrote:

Do you have a compiler/IDE, VB for example?

"fsanchez" <fsanchez (AT) discussions (DOT) microsoft.com> wrote

Yes,
I have Visual Basic 6.0 installed...

"Darren Green" wrote:

Tools & Tasks
(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" <fsanchez (AT) discussions (DOT) microsoft.com> wrote

Hi Darren,
I am getting error while accessing the DTS ActiveX Helper webpage. The
error indicates that the web page does not exist.

"Darren Green" wrote:

Hi Darren,
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" wrote:

As you now know, my mistake, and now fixed.

In message <333B5400-62DA-41EE-A999-6196D14B52C5 (AT) microsoft (DOT) com>,
fsanchez <fsanchez (AT) discussions (DOT) 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

Darren,

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")