SSIS copy all files into a variable destination via execute process task

Question

I have read several posts that have given examples of similar processes. I have put a lot of time trying to make this work so i would really like to get this to work so I can put all this time into the "I learned something Category" But if its just not possible... I guess I will learn that at least.

I am tring to copy all files from a folder to another folder using ssis 2008r2. -using execute process task -using C:\Windows\System32\cmd.exe in the executable field -the destination folder is a variable I want to create using the starttime variable - which is working -then i want to copy all the working files into that folder. -this dest folder uses the start time down to the second so i can capture each attempted run with minimal errors or overwrites

With so many attempts and failures i have dumbed it down to using straight string variables to try to just move all the files in the simplest way that still uses a variable. the variables I am currently using in my mutiple failed attempts are...

[user::testtofolder] = p:\Utilities\SWUMOps\Archive\20160720-0921-03-SAWSImports
[user::testtofolder1] = p:\\Utilities\\SWUMOps\\Archive\\20160720-0921-03-SAWSImports
[user::testtofolder2] =  p:\\Utilities\\SWUMOps\\Archive\\20160720-0921-03-SAWSImports

The below are some of the attempts I have tried in the arguments field

/c copy /y p:\utilities\swumops\backend\saws p:\utilities\swumops\archive\20160720-0921-03-sawsimports  -- works (no variables)

/c copy /y p:\utilities\swumops\backend\saws + @[user::testtofolder]  --  fails

"/c copy /y \"p:\utilities\swumops\backend\saws\" + @[user::testtofolder]  --  fails

"/c copy /y \"p:\utilities\swumops\backend\saws\"" + @[user::testtofolder] -- fails

"/c copy /y \"p:\utilities\swumops\backend\saws \"" + @[user::testtofolder]  -- fails

"/c copy /y \"p:\\utilities\\swumops\\backend\\saws \"" + @[user::testtofolder]  --fails

/c copy /y p:\\utilities\\swumops\\backend\\saws + @[user::testtofolder]  --fails

"/c copy /y \"p:\\utilities\\swumops\\backend\\saws \" + @[user::testtofolder]  --fails    feel this is the closest

"/c copy /y \"p:\\utilities\\swumops\\backend\\saws \" + @[user::testtofolder1]  -- fails

"/c copy /y \"p:\\utilities\\swumops\\backend\\saws \" + @[user::testtofolder2] -- fails

the frustration thing about this is I can't(don't know how to) see what is truly passed to cmd because the window flashes to fast and the errors only read the line that is in the argument field not the past string. the errors look like this...

Error: 0xC0029151 at Copy files to archive, Execute Process Task: In Executing "C:\Windows\System32\cmd.exe" ""/c copy /y \"p:\utilities\swumops\backend\saws \" + @[user::testtofolder]" at "", The process exit code was "1" while the expected was "0".

Hope I put enough details. Let me know if you need more. Thank you

I've used this method in the past and it worked fine. Let me dig it up. IMHO the SSIS File system task requires way too many variables and is not flexible enough. — Nick.McDermaid
OK I don't have the package unfortunately. I suggest the first step in troubleshooting is to evaluate the command line beforehand in a seperate variable in an Evaluate Expression task. Then inside here you can press the evaluate button, copy and paste directly into a cmd window (without the /C) and see the error. — Nick.McDermaid
I have been using this method. Gald you confirmed that that should match up with the way the argument line will read it. I think my problem may be other that just syntax since i dumbed it down even more. I switch to to xcopy.exe like cqi mentioned (it does seem more simple and straight forward) then I put the whole string in a variable and just put the variable in the argument by it self and it still fails the same way. — Kaz
Yes it's just syntax and confounded quote escaping and that kind of stuff. The only way you are going to sort it is work it out beforehand in a variable, copy paste into a CMD window and observe the error. And it will take many iterations! You definitely need. This question stackoverflow.com/questions/26598274/copy-directories-with-ssis has an example of how you should set up XCOPY in an execute process task — Nick.McDermaid
OK now I'm actually looking at your expression attempts... possibly your only issue is a missing space... which you would see if you put the expression into and evaluate expression task and ran it.. for example try this: "/c copy /y p:\utilities\swumops\backend\saws\ " + @[user::testtofolder]. Note the space between saws\ and the trailing quote — Nick.McDermaid

cqi cqi · Accepted Answer · 2016-07-21T03:46:52

In SSIS File System task should be a much easier way to do your task. I am not sure how come you need to use command line to move/copy the file.

For your question, I believe you need to use xcopy.exe instead of cmd.exe. The executable should be "C:\Windows\System32\xcopy.exe", and arguments should be looked like

" /y p:\utilities\swumops\backend\saws " + @[user::testtofolder]

Hope it helps.

SSIS copy all files into a variable destination via execute process task

2 Answers