sql server dts: Executing DTS from VB.net -- sql server dts

Executing DTS from VB.net

ninel gorbunov via SQLMonster.com
8/19/2005 12:00:00 AM

sql server dts: 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:
[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
[/code]

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

--

Re: Executing DTS from VB.net

Reza Alirezaei
8/19/2005 9:53:22 PM

Is it an asp.net application?

Reza Alirezaei
http://blogs.Devhorizon.com

[quoted text, click to view]