I've managed to get an 'Execute Process Task' package in SSIS to call the winSCP.com application and run a command against it to use a script, this then guides winSCP to transfer a file onto a remote SFTP location.
I'm currently only testing so far using a local FTP but in theory all would work when I move this to an SFTP platform.
This issue comes when the file has successfully transferred and the program exits; I set the return code on success as 0 (within SSIS) - specified on this website it should be. However, when executing the task in SSIS this fails; despite the log showing an exit code of 0 (see below!).
Changing this success code to 1 seems to give it all clear but was wondering if winSCP is returning the wrong code (presume not) or if the process task is returning a 1 as it has successfuly called winSCP?
Logs from SSIS;
SSIS package "TranferFile.dtsx" starting.
SSIS package "TranferFile.dtsx" finished: Success.
The program ' TranferFile.dtsx: DTS' has exited with code 0 (0x0).
I noticed that the use of winSCP with SSIS isn't clearly documented either so if requested I'll pop together a quick guide on what to put where etc for those checking these boards. SSIS is a complicated mother! :P