the user executing this package must have execute rights to the sp_OA* sps
in master.
----
CREATE PROC <SP Name> as
DECLARE @hr int, @oPKG int
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:
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS
Package Name>")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
--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
---
-----Original Message-----
From: Joel Firestone [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 4:06 PM
To: SQL
Subject: calling DTS from a Stored Procedure
Everyone:
I recently created a DTS package that imports data into the db (SQL 7).
I'm looking to see if I call that package from within a SP? The scheduler
has been less than reliable.
TIA.
Joel
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
