DTS and VB .NET --Capturing Return Value of stored proc

#1

I am executing a DTS package from vb.net. This package executes a stored proc
which is supposed to return a return value.

Here is my .net code:
Code:

Code:

Dim oPkg As DTS.Package2
Dim oStep As DTS.Step2
Dim results As Boolean

results = True
oPkg = New DTS.Package2

oPkg.LoadFromSQLServer(ServerName:="NINEL-D246655F1", ServerUserName:
="timecontroluser", ServerPassword:="timecontroluser", PackageName:
="ExportTSRLog")

oPkg.GlobalVariables.Remove("sCalldate")
oPkg.GlobalVariables.AddGlobalVariable("sCalldate", sCalldate)

For Each oStep In oPkg.Steps
oStep.ExecuteInMainThread = True
Next

oPkg.FailOnError = True
oPkg.Execute()

For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTS.DTSStepExecResult.
DTSStepExecResult_Failure Then
results = 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

I can not figure out how to capture this value on the vb.net side.
Do I need to do something special in the DTS package aside from just setting
the reeturn value in the stored procedure?

Can anyone help?

Thanks,
Ninel

--
Message posted via http://www.sqlmonster.com

#2

You can use a Script task to capture the value to a Global Variable. In
your VB.Net code you capture the global variable fter the package has
executed.

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)

Allan

"ninel gorbunov via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Quote:

I am executing a DTS package from vb.net. This package executes a stored
proc
which is supposed to return a return value.

Here is my .net code:
Code:

Code:

Dim oPkg As DTS.Package2
Dim oStep As DTS.Step2
Dim results As Boolean

results = True
oPkg = New DTS.Package2

oPkg.LoadFromSQLServer(ServerName:="NINEL-D246655F1",
ServerUserName:
="timecontroluser", ServerPassword:="timecontroluser", PackageName:
="ExportTSRLog")

oPkg.GlobalVariables.Remove("sCalldate")
oPkg.GlobalVariables.AddGlobalVariable("sCalldate", sCalldate)

For Each oStep In oPkg.Steps
oStep.ExecuteInMainThread = True
Next

oPkg.FailOnError = True
oPkg.Execute()

For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTS.DTSStepExecResult.
DTSStepExecResult_Failure Then
results = 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

I can not figure out how to capture this value on the vb.net side.
Do I need to do something special in the DTS package aside from just
setting
the reeturn value in the stored procedure?

Can anyone help?

Thanks,
Ninel

--
Message posted via http://www.sqlmonster.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS and VB .NET --Capturing Return Value of stored proc

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS and VB .NET --Capturing Return Value of stored proc - 08-18-2005 , 09:12 AM

Re: DTS and VB .NET --Capturing Return Value of stored proc - 08-18-2005 , 12:40 PM