Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ASP called SQL Server DTS package runs it 2x

Status
Not open for further replies.

dkern

Programmer
Feb 27, 2003
4
US
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(&quot;ADODB.Connection&quot;)
DataConn.Open myDSN
pcuserid = &quot;DBUsername&quot;
pcpassword = &quot;DBPassword&quot;
strQuery = &quot;exec usa_runimportcreditdtspkg @importfilename= '&quot; & psFileName & &quot;', @userid= '&quot; & pcuserid & &quot;',@password='&quot; & pcpassword & &quot;' &quot;
Set rsMain = Server.CreateObject(&quot;ADODB.RecordSet&quot;)
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(&quot;WEBDEV1&quot;, &quot;'+rTrim(@userid)+'&quot;, &quot;'+rTrim(@password)+'&quot;, 0, , , , &quot;importcredits&quot;)'

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(&quot;ImportFilename&quot;).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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top