Running DTS Package from a Stored Procedure

[mashaguen]

I have to execute a DTS package from a stored procedure.

THis is my stored procedure:
-------------------------------------------------------
CREATE PROCEDURE dts_staging_to_production AS

EXEC xp_cmdShell 'dtsrun /S[servername] /U[username] /P[password] /N[dts package name]

GO

---------------------------------------------------------
I am not giving the server name, etc for obvious reasons.

My DTS package runs fine from dtsrun. But it does not run from the stored procedure. Could it be a permissions thing? I think that only memebers of the sysadmin fixed server role can execute xp_cmdshell.

Is this correct? or is there another problem?

 

[VBXL]

Have you fixed this problems as i have the same problem

 

[dbmsguy]

what kind of error do you get??

i have run DTS packages out of a SP, but the times i have done it i have used dynamic sql. ran just fine.

try doing it through dynamic sql and see what happens. Paul

http://www.dbmsguy.com