SSIS Expression builder for MULTI line file ( Exec Process Task)

Question

My global task to create config file for WinSCP process, and it should be multi lines (no options). I try to use Expression builder for CMD/Echo to do this. And still file, tried all option with and without /C flag which is critical for SSIS cmd task. I have option to have task for each line and it's by back plan, but trying to see if I can come up and learn how to do this in single box. I example below I can create multi line Expression using + " " combo where second quotes on new line. But it's still not good for whole task, Looks like CMD /C need single line ??

All info displayed in pic below:

This is arguments I'm using now with WinSCP executable in System task.

open [email protected]
option transfer binary
put c:\SFTP\Magda_Members_20190901.csv   "/Business Intelligence Share/Zbignev/"
close
exit

Answer 1

Creating multi-line expression

In order to add new lines in SSIS expression you must use the carriage return and line feed characters \r\n:

"/C Echo line11 \r\n /C Echo " + @[User::Var3] + "Line222 \r\n /C Echo Line3 > Config.dat" 

Executing multiple commands

If adding new lines didn't work, you can use && operator to run multiple command using CMD:

• SSIS: Execute Multiple Commands with 1 line in Execute Process Task (Arguments)

Workaround

If all what I mentioned above didn't work, then try saving the whole command into a batch file, and execute this batch file from the Execute Process Task.

Answer 2

You can write two lines using this syntax:

/c "echo line1 && echo line2" > config.dat

---

Though, you do not need to create a config file for WinSCP. All WinSCP options can be configured on its command-line. So this may be XY problem.

And while it's not clear, you may actually be creating a script file, not a config file. You can instead specify all WinSCP commands on WinSCP command-line using /command switch:

winscp.com /command "open [email protected]" "option transfer binary" "put c:\SFTP\Magda_Members_20190901.csv ""/Business Intelligence Share/Zbignev/""" "close" "exit"

WinSCP GUI can generate a command-line sample for you.

---

See also WinSCP article SFTP Task for SSIS/SSDT.

Though you actually better use WinSCP .NET assembly instead of scripting in SSIS. See Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS).