5

I've got a bunch of simple SSIS packages that output SQL data to excel, then I rename the files and move them out to the end user.

Some of them use file paths with spaces in them, and they aren't excited about using new folders. Here's the code I'm using:

    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

but it wouldnt work without the underscores.

Question how can I make this work without underscores?

8

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.

6

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.

  • Not sure if "limit" is the right word. Perhaps it is, but I'd say it's rather a peculiarity of how cmd processes double quotes in a command line. I believe this question is essentially about the same problem and jeb's answer explains what is going on. – Andriy M Apr 26 '18 at 19:18
  • Agreed. And you can use Script tasks + Package/Project variables to make dynamic paths if needed. – MguerraTorres Apr 26 '18 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.