Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTEXEC command works from command prompt, does not work from xp_cmdshell Expand / Collapse
Author
Message
Posted Monday, May 7, 2012 3:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:22 AM
Points: 2, Visits: 44
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
Posted Wednesday, July 11, 2012 11:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:04 AM
Points: 22, Visits: 123
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 »

Add to briefcase

Permissions Expand / Collapse