executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.