I've got a COM+ component that is called by my ASP code.
The COM+ component passes a global variable to a DTS
package and executes the DTS package. I need to edit the
COM+ component to get it to accept the global variable
and to pass it on. Herein lies my problem. I am not
savvy in the ways of COM+ and I am uncertain how to do
this. I am using an example I found online and I believe
it to be built in VB6. I am running Visual Studio.net.
Is it possible to load the component into VS.net without
comprimising the component? If it is...great! Also, how
would you package a component in vs.net? I've been
openining a .vbp file in visual studio.net, upgraded it,
but I've been unable to find a way to compile it.

Finally, the code. Here is the example code I downloaded
(after vs.net upgrade):


Public Function Execute(ByVal sServer As String, ByVal
sPackageName As String) As Boolean
On Error GoTo Err_Handler
Dim oPKG As DTS.Package
Dim oStep As DTS.Step
oPKG = New DTS.Package

Dim lErr As Integer
Dim sSource, sDesc As String

Execute = True

' Load Package
oPKG.LoadFromSQLServer(sServer, , ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConne
ction, , , , sPackageName)

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next oStep

' Execute
oPKG.Execute()

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then

oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
m_sError = m_sError
& "Step """ & oStep.Name & """ Failed" & vbCrLf
& "Error: " & lErr & vbCrLf & "Source: " & sSource &
vbCrLf & "Description: " & sDesc & vbCrLf & vbCrLf
Execute = False
Else
m_sError = m_sError
& "Step """ & oStep.Name & """ Succeeded" & vbCrLf &
vbCrLf
End If
Next oStep

oPKG.UnInitialize()

Clean_Up:
'UPGRADE_NOTE: Object oStep may not be
destroyed until it is garbage collected. Click for
more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?
keyword="vbup1029"'
oStep = Nothing
'UPGRADE_NOTE: Object oPKG may not be
destroyed until it is garbage collected. Click for
more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?
keyword="vbup1029"'
oPKG = Nothing

Exit Function

Err_Handler:
Execute = False
m_sError = "Error in Object Execution" &
vbCrLf & "Number: " & Err.Number & vbCrLf & "Source: " &
Err.Source & vbCrLf & "Description: " & Err.Description
GoTo Clean_Up
End Function




================================================== ========
===========
I'm trying to modify it to something like this to get the
dts package to accept the global variable:

Public Function Execute(ByVal sServer As String, ByVal
sPackageName As
String, ByVal sTextFile As String) As Long

Dim oPkg As DTS.Package2
.......
oPkg.LoadFromSQLServer sServer ... sPackageName

oPkg.GlobalVariables("VariableName").Value = sTextFile

oPkg.Execute

......

End Function



Unfortunately, the line:

oPKG.GlobalVariables("VariableName").Value = sTextFile

shows the error:

Interface 'DTS GlobalVariables' cannot be indexed because
it has no default value


What am I missing here? Please help as this is
urgent!!! Thank you

You can not do Visual Basic 6.0 using Visual Studio .NET
You either have to get VB 6.0 or convert to Visual Basic .NET.

Why are you using COM+ to call a DTS package? What is the added benefit? I
can not see any but increased overhead. You would be better of calling it
from ASP directly.

See http://www.sqldts.com/default.aspx?6,104,207,7,1 for an example "Execute
a package from Active Server Pages (ASP)"

GertD (AT) SQLDev (DOT) Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.

"Jeff Borden" <jeff_borden (AT) hotmail (DOT) com> wrote

I would agree that calling it from asp would be better.
I think the reason I was using the COM+ to call it was
for security privileges reasons (can't exactly remember
tho). Here is the posting:

http://communities.microsoft.com/new...previewFrame.a
sp?
ICP=msdn&sLCID=us&sgroupURL=microsoft.public.sqlse rver.dts
&sMessageID=%253CdqUzjTVV5VM%2524EwVA (AT) sqldts (DOT) com%253E

In article <OIf#xfOYDHA.2464 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Gert E.R. Drapers
<GertD (AT) Online (DOT) SQLDev.Net> writes

Gert,

The reason why I suggest using COM+ whith ASP classic is that the COM+
package identity can be set, and hence the DTS package can be set to run
under an account other than IWAM_MACHINE. IWAM_MACHINE is always a local
account and this does not have permissions to connect to SQL server, or
to access a file through a UNC path. (Assuming you separated IIS and SQL
onto two machines, but UNC issues will always be there). Many people
when executing DTS from ASP need greater permissions that would be
normal for IWAM_MACHINE, or most importantly permissions only available
through a domain account.
COM+ packages give use the ability to abstract away from IIS and it's
security.

If you have the option of using .Net and more specifically ASP.Net then
I'm sure there are much better ways of doing this, but I haven't tried,
nor was .Net raised in the original post.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

I'm calling the COM+ component from ASP(not .Net). I
just need to edit the component so it will accept the
Global Variable. I don't care if I do it from Notepad.
It just has to be done. The company I am doing this for
is tired of waiting on me. PLEASE SOMEONE TELL ME HOW I
CAN COMPLETE THIS!!! I don't care if I drop the COM
component, I don't care if I repair it with duct tape. I
just need a rememdy for this... Thank you for your
assistance.