Call DTS Package from ASP via SP runs it 2x. Runs fine from w/in SQL

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,invoicenoHere's a few ideas...I'd try each of these steps in order one at a time until you get the performance you want.

1) It looks like you are using a DSN as your connection when you create the connection object. First, I'd try using the OLE DB for SQL Server instead. I have found that using the OLE DB provider, rather than a DSN or a "DSN-less" connection, is a lot faster. Either way, you want to do this step. By using an OLE DB connection, you won't need a DSN at all for the ASP connection. This has its advantages too.

2) Next, I would explicitly tell the Recordset object that I am executing a stored procedure, rather than passing SQL that states "EXEC spXXXX", by using the adCmdStoredProc constant in the last argument of the Open method. You may see some improvements there too.

3) Last, try using a command object with the recordset object since you are executing stored procedures.

Here is some sample code to get you started...

' try these one at a time, but build them in order. Do step 1 AND step 2, or do step 1 AND step 3
Dim strcon, con, rst, cmd ' need cmd object variable only if you try step 3

' >>> step 1
' create string to use to connect to SQL Server
strcon = "Provider=SQLOLEDB; Data Source=ServerName; Initial Catalog=DBName; User ID=UserName; Password=Password;"

' instantiate connection object
Set con = Server.CreateObject("ADODB.Connection")

' open connection to SQL Server using the connection string
con.Open strcon

' >>> step 2
' instantiate recordset object
Set rst = Server.CreateObject("ADODB.Recordset")

' open the recordset object (execute the stored procedure)
' you need to use the integer constants for the CursorType, LockType, and Options arguments in ASP (sorry, I don't know them off hand)
rst.Open "sp_name", con, CursorType, LockType, adCmdStoredProc

' >>> step 3
' using the command object can be a bit tricky. I never tried it in ASP, just VB 6, so I'm only guessing on my conversions since the syntax is totally different...

' instantiate command object
Set cmd = Server.CreateObject("ADODB.Command")

' set command type and command text
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_name"

' create and set the parameter values (only if there are input parms for the sp) otherwise, you don't need these next two groups of code...
cmd.Parameters.Append("parm1", DataType, adParamInput)
cmd.Parameters.Append("parm2", DataType, adParamInput)
cmd.Parameters("parm1").Value = "value for first parameter"
cmd.Parameters("parm2").Value = "value for second parameter"

' associate the command object with the connection object
cmd.ActiveConnection = con

' open the recordset object (execute the stored procedure)
Set rst = cmd.Execute

Another thing is that you may want to try tweaking the CursorType and LockType arguments whenever you call the Recordset.Open method for either steps 2 or 3 depending on what you want to do with the recordset. Be sure to only use the minimum amount of locking or cursor fetching capabilities you absolutely need to maximize performance.

Lastly...whew! With regards to VBScript and ASP, you may want to use the Response.Buffer and flush it at times depending on how you want to display the page in the browser.

If you have any questions, feel free to ask.

HTH!

Kael