How do you write to a folder with spaces in it?

You should (pretty much always) enclose paths inside quotation marks. So, like this:

declare @date varchar(25),
@sql varchar(1000);

set @date = cast(datepart(month, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(day, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(year, current_timestamp) as varchar(4));
print @date;

set @sql = 'copy "D:\Data\ED_72.xlsx" "\\ehsintra3\ED 72 weekly\ED 72 ' + @date + '.xlsx"';
print @sql;

exec  xp_cmdshell @sql;

Putting paths inside quotation marks ensures the command-processor can understand where a path starts and ends.

Also, instead of using copy you probably want to look at either robocopy or xcopy. The copy command is brutally weak and frail.

Also, a minor note, add semi-colons to the end of your statements in T-SQL.


The other approach is to use the File System Task within SSIS itself. It handles "complex" path and file names without the rigmarole and risks of xp_cmdshell escaping.

What risk? There's a limit on double quoted arguments.

See also Escaping command parameters passed to xp_cmdshell to dtexec on Stack Overflow.