Hi all,

I copy the following stored procedure from an article. It is supposed to run a dts package. The dts package simply exports a data set to a certain path in my network. When I run the package manually from the enterprise manager it runs succesfully. However, when I run the following stored proc., even though I don't get any errors, my file or data set is never exported. I already check my security settings and used several combinations of users/passwords that have all possible rights in SQL. Can anybody help?
Here is the stored procedure:

CREATE PROCEDURE sp_ExecuteDTSPackage
    @PkgName varchar(255)
AS

DECLARE @hr INT, @oPKG INT, @Cmd VARCHAR(1000), @RetVal INT
-- Create Package Object
EXECUTE @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    PRINT ' ** Create Package Object Failed **'
    PRINT ''
    EXEC sp_OAGetErrorInfo @oPKG, @hr
    RETURN
END
-- Load the package
SET @Cmd = 'LoadFromSQLServer("(local)" ,"" ,"" ,0 , , , ,"' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
    PRINT ' ** Load Package Failed **'
    PRINT ''
    EXEC sp_OAGetErrorInfo @oPKG, @hr
    RETURN
END
-- Execute package
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
PRINT @PkgName
PRINT @RetVal
IF @hr <> 0
    BEGIN
        PRINT ' ** Execute Package Failed **'
        EXEC sp_OAGetErrorInfo @oPKG, @hr
        --RETURN
    END
ELSE
    PRINT ' ** Execute Package Succeeded **'

-- Clean up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    PRINT ' ** Destroy Package Failed **'
    EXEC sp_OAGetErrorInfo @oPKG, @hr
    RETURN
END
GO

Hey,

There is an easier way to do this, check out the dtsrun utility. This utility is a command-line prompt program that can be run using xp_cmdshell. You can read more about it in books online.

Brian

Thanks, you are right, that's easier. In any case I already figured out what the problem was. My SQL Server service was running under a local account with no access to my network. There is a good article on DTS security that covers different security scenarios (Microsoft Knowledge Base 269074).