[dkern]

I have a DTS package that is called from a SP in SQL Server 2000. When it is run from within SQL, the profiler shows that the Application "DTS Designer" is only called once and does it's tasks and returns the recordset.....

When the SP is called from via ASP, the profiler shows that the Application "DTS Designer" actually runs 2x before returning... asside from killing efficiency, the extra run is also causing my page to take forever (15 minutes)

ASP Call:
myDSN=<DSNString> (works fine...)
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open myDSN
pcuserid = "DBUsername"
pcpassword = "DBPassword"
strQuery = "exec usa_runimportcreditdtspkg @importfilename= '" & psFileName & "', @userid= '" & pcuserid & "',@password='" & pcpassword & "' "
Set rsMain = Server.CreateObject("ADODB.RecordSet")
rsMain.Open strQuery,DataConn,1,3


Here is the SP code:
declare @hr as int,
@opkg as int -- the object token that will refer to the created PKG

--Creating the DTS Package Object:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Loading the Package:
declare @loadstring as varchar(250)
set @loadstring = 'LoadFromSQLServer("WEBDEV1", "'+rTrim(@userid)+'", "'+rTrim(@password)+'", 0, , , , "importcredits")'

EXEC @hr = sp_OAMethod @oPKG,@loadstring, NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

-- clear out the table before proceeding if it exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportCredits]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
delete from ImportCredits where employeeSSN is not null
END

-- use the passed filename to set the global variable inside the pkg
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("ImportFilename").Value', @importfilename

--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END

--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

select <Fields>
from <DTSPopulatedTable> i
where substring(invoiceno,1,3)<> 'PSI' and amount > 0
order by customer,employeessn,invoiceno