A while ago, I started using WinSCP to download files every morning from an SFTP server for processing in my SSIS package. This was set up with an Execute Process task with the following settings:
Executable: C:\Program Files (x86)\WinSCP\WinSCP.exe
Arguments: /script="C:\SSISPackages\SFTP_Upload_Script.txt" /log="C:\SSISPackages\WinSCP_Log.txt"
For the task that downloads from the SFTP server, the job works perfectly and has been reliable for about a month or so. Recently I got a new requirement to upload some files to the same server, but using different credentials that default to a different ftp directory. This is when the trouble started!
I have a new SSIS package that now uses WinSCP to upload files to the SFTP server. My Execute Process task looks the same as the one in the SSIS package that runs reliably every day. The only difference that I can see is in the script file, which includes a different username/password and instead of a get command line, it contains a put line. Whenever I run this package interactively, it works, no errors. However, when it runs unattended via a scheduled SQL Server Agent job, I keep getting the following error when it reaches the Execute Process task for WinSCP:
Execute Process Task Description: An error occurred with the following error message: "No such interface supported".
This error has not always been reproducible. Last night I successfully scheduled the SQL Server Agent job, logged off the server and a couple of minutes later got the email notification that confirmed successfuly job completion. I checked the server and sure enough it all worked fine. I rescheduled the job to run at 09:30 this morning, expecting the job to be fine, but to my frustration I got the failure message and the dreaded "No such interface supported" message in the history log for the job.
I'm struggling to understand why this is happening and why it always seems to work fine when I run it interactively. After today's failure, I logged on to the server, opened up SSMS and started the SQL Server Agent job in question, which completed fine with no errors.
Has anyone got any experience with this kind of problem? I wish I could use the built in FTP task, which has proved reliable for me in the past but unfortunately the transfer requirements are for SFTP only and there is no built in SSIS task that can handle this, forcing me to look at external tools. WinSCP seems a good one and I latched onto it after finding web articles describing other people using it in their SSIS packages.
I was wondering if this error might be something to do with multiple threads. If a previous WinSCP process (for example the "get" task that downloads a file from the SFTP server - which is scheduled to run earlier in the morning) fails for some reason to terminate gracefully, might this be the cause? Should I look at the taskkill process with the /F switch to force termination of any running instances of WinSCP before attempting execution of a new process? This seems a little drastic but at the moment I'm happy to try it! ;-)
If anyone has any insight into this or similar problems running external processes from within SSIS packages, I will be most grateful for your advice.
Many thanks in advance.