DTEXEC command works from command prompt, does not work from xp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Bird » DTEXEC command works from command prompt,...

DTEXEC command works from command prompt, does not work from xp_cmdshell

Rate Topic

Display Mode

Topic Options

Author

Message

mikeebertsr

Posted Monday, May 07, 2012 3:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:17 PM
Points: 2, Visits: 41

Hello. This is my first time asking a question here. I hope I give enough detail to explain the problem.

I have a DTEXEC command that is built on the fly by a stored proc. Here it is:

dtexec /F \\lislefs1\filearchives\HSBSSouth\Datawarehouse\SSIS\SSIS_HLX_Extract\SSIS_HLX_Extract.dtsx /CONFIGFILE c:\users\mebert\default.dtsconfig /SET "\package.Variables[User::Source].Properties[Value]";"\"SELECT top 9 line_text line_text FROM ##HLXElg_extract154""

When I paste this command into a DOS command window and run it, it runs perfectly. When I try to run it from a query window, it fails with this error:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

This happens when I run the dtexec directly from a query window and also when I run it from xp_cmdshell.

I thought about putting the SSIS package into a SQL Server Agent job and calling the job instead. I'm pretty sure that would work, but I can't allow the proc to continue processing until the package finishes.

Please let me know if you need any more info. Any help would be appreciated.

Thanks

Mike

Post #1296139

ronang

Posted Wednesday, July 11, 2012 11:58 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 11, Visits: 64

Hi Mike

What version of SQL Server are you using?
What I would do in this situation is actually look at the string being passed to the cmdshell by adding a select after you have created the DTEXEC command to show the exact string you are asking SQL to execute. I don't think you are passing the command in the following format
EXEC xp_cmdshell 'DTEXEC Command'.
First eliminate the syntax as a problem, then we can begin to work on the rest of the possible issues.

Post #1328677

« Prev Topic | Next Topic »

Permissions