p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server DTS (http://p2p.wrox.com/forumdisplay.php?f=108)
-   -   Running DTS package from withing a stored proc (http://p2p.wrox.com/showthread.php?t=15968)

flaco July 26th, 2004 05:33 PM

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



bmains July 26th, 2004 06:02 PM

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

flaco July 27th, 2004 05:34 PM

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).




All times are GMT -4. The time now is 04:04 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.