Executing DTS from VB.net

[ninelgorb]

I am executing a dts package from .net. I pass a global variable to the dts package and it executes a stored proc and then exports to a text file.

When populating the global variable from within the dts package and executing it, the package executes successfully. But when I try to execute it from .net I get the following error: "The task reported failure on execution".

I can't figure out how to get more detailed information about the error.

Here is my code:

Public Function ExecuteDTS(ByVal sCalldate As String, ByRef sMessage As String) As Boolean
        Dim oPkg As DTS.Package2
        Dim oStep As DTS.Step2

        Dim oGlobalVar As DTS.GlobalVariable2
        Dim results As Boolean
        Dim variable As String
              
        ExecuteDTS = True

        results = True
        oPkg = New DTS.Package2

        oPkg.LoadFromSQLServer(ServerName:="127.0.0.1", ServerUserName:="timeuser", ServerPassword:="timeuser", PackageName:="ExportTSRLog")


        oPkg.GlobalVariables.Remove("sCalldate")
        oPkg.GlobalVariables.AddGlobalVariable("sCalldate", sCalldate)
        
        Dim lErr As Int32
        Dim sSource As String
        Dim sDesc As String

            For Each oStep In oPkg.Steps
                oStep.ExecuteInMainThread = True
                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

            Next
            oPkg.FailOnError = True
            oPkg.Execute()

            Dim i As Int32
            Dim IDofInterfaceWithError As Int64
            Dim HelpContext As String
            Dim HelpFile As String
            Dim Description As String
            Dim Source As String
            Dim ErrorCode As Int64
            Dim bStatus As Boolean
            For Each oStep In oPkg.Steps
               If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                    bStatus = False
                    ' Retrieve the error information for that step.
                    Call oStep.GetExecutionErrorInfo(ErrorCode, _
                                                     Source, _
                                                     Description, _
                                                     HelpFile, _
                                                     HelpContext, _
                                                     IDofInterfaceWithError)

                    Response.Write("<BR>The """ & oPkg.Name & """  package """ & _
                           oPkg.Name & """ failed." & Chr(13) & Chr(13) & _
                          "ErrorCode: " & CStr(ErrorCode) & Chr(13) & _
                          "Source: " & Source & Chr(13) & _
                          "Description: " & Description & Chr(13) & _
                          "HelpFile: " & HelpFile & Chr(13) & _
                          "HelpContext: " & HelpContext & Chr(13) & _
                          "IDofInterfaceWithError: " & IDofInterfaceWithError)
                    ExecuteDTS = False
                End If
            Next

            If results = True Then
                lblResult.Visible = True
                lblResult.Text = "TSRLogs have been exported"
            End If
            oPkg.UnInitialize()
            oStep = Nothing
            oPkg = Nothing
            ExecuteDTS = results
End Function

It doesn't even go into the If condition: If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then...

How can I go about figuring out what is causing the error?

Thanks,
Ninel

 

[Meleagant]

Did you ever figure this out? I am having a similar problem executing a DTS package from VB.Net

Journeyman -- The Order of the Seekers of Truth and Penitence