View Single Post
  #1 (permalink)  
Old July 26th, 2004, 05:33 PM
flaco flaco is offline
Registered User
 
Join Date: Dec 2003
Location: Miami, FL, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Running DTS package from withing a stored proc

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