none
xp_cmdshell problem

    Question

  • I'm trying to start a ssis package by using xp_cmdshell.
    When I execute the statement it just hangs.
    
    To find the source of the problem I've done the following
    exec master.dbo.xp_cmdshell 'dir c\'  : no problem
    exec master.dbo.xp_cmdshell 'c:\testdir.bat' (where testdir.bat contains an 
    "mkdir c:\test" command) : no problem
    
    I put the dtexec command with all it's parameters in a testdtexec.bat file 
    on the server and executed it from the server: no problem
    
    When I then run the command:
    exec master.dbo.xp_cmdshell 'c:\testdtexec.bat'
    -> a "cmd" process is started (with a user that has admin priv)
    -> a "dtexec" process is started (with the same user runs sqlagent)
    and then it hangs forever
    
    Any hints would be appreciated

     

    Wednesday, June 11, 2008 6:25 AM

Answers

  •  Antoon Vansina wrote:

    Ok, but the I want to pass a parameter to ssis package, I don't think I can do that (directly) when I use SQL Agent.

    I could put the parameter in a configuration table and then start SQL Agent job, I'll use that as a workaround.

     

    the entire dtexec command, including switches, can be included as a sql server agent job step.

     

    commands executed by the sql server agent cmdexec subsystem work exactly like commands executed by xp_cmdshell.

     

    hth

    Friday, June 13, 2008 7:29 AM

All replies

  • i strongly suggest that you employ sql server agent's cmdexec subsystem instead of xp_cmdshell because sql server agent provides better security.

     

    hth

    Wednesday, June 11, 2008 6:32 AM
  • Ok, but the I want to pass a parameter to ssis package, I don't think I can do that (directly) when I use SQL Agent.

    I could put the parameter in a configuration table and then start SQL Agent job, I'll use that as a workaround.

     

    But I would like to find out why xp_cmdshell isn't working.

     

    Wednesday, June 11, 2008 8:16 AM
  •  Antoon Vansina wrote:

    Ok, but the I want to pass a parameter to ssis package, I don't think I can do that (directly) when I use SQL Agent.

    I could put the parameter in a configuration table and then start SQL Agent job, I'll use that as a workaround.

     

    the entire dtexec command, including switches, can be included as a sql server agent job step.

     

    commands executed by the sql server agent cmdexec subsystem work exactly like commands executed by xp_cmdshell.

     

    hth

    Friday, June 13, 2008 7:29 AM