Escaping command parameters passed to xp_cmdshell to dtexec

Question

I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell:

declare @cmd varchar(5000)
set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;c:\foo.xlsx'
print @cmd
exec xp_cmdshell @cmd

This works fine, however I can not guarantee the variable value (c:\foo.xslx) is not going to contain spaces so I would like to escape that with quotes like below:

set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;"c:\foo.xlsx"'

But by doing this I get the error

'C:\Program' is not recognized as an internal or external command, operable program or batch file.

Both of the above commands work fine if executed within cmd.exe so I am guessing that SQL Server is interpreting my double quotes and changing something, but I can't figure out what.

Answer 1

After looking into this, it appears you have to use the DOS 8.3 notation with xp_cmdshell e.g. c:\progra~1... and you can only have one set of quotes on the arguments.

To get around this limitation, either use the older DOS notation, or put your code in a batch file instead which will run fine.

Source: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4e7024bb-9362-49ca-99d7-1b74f7178a65