Can you start a dts from a stored procedure?

[TALE]

I'm trying to create a stored procedure that will envoke dts in SQL 7.  Is this possible?

 

[tjosh]

the user executing this package must have execute rights to the sp_OA* sps in master. This code calls some sp_s in the master database that do not exsist on a default install. You must run it get the error and then look up the SPs in Books online and create them. Hope this helps and if you have any questions please contact me. I know that this guys post was a year ago but others may have the same question. Be careful with this code.
----

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(&quot;<servername>&quot;, &quot;<user>&quot;, &quot;<password>&quot;, 0, , , , &quot;<DTS Package Name>&quot',
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
---