SSIS SFTP task problems

Hi,

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.
irb56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlexPaceCommented:
When something works interactively but not as a service:
75% Permissions are different for current user vs. Service Account (or LocalSystem or Network Service)
20% Desktop/Interaction example: the process is trying to display a messagebox but has no "desktop"
5% Computer is possessed by evils spirts, exorcism is required

You might consider trying Robo-FTP as a SFTP client.  Unlike WinSCP It costs money but it has a 30-day eval so you can see if it works for you.  It will allow you to set a timeout on the login, download or upload operation so you shouldn't get the hang ups.  Also you could use the COM interfact if you want instead of making a script and launching it.  But I digress....

Can you set it up as a job that runs as the same account you use when you run it interactively and see what happens?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
irb56Author Commented:
Thanks for your suggestions. The SQL Server Agent job runs under a proxy account, which is linked to a domain account. I have just logged on to the server with the same domain account, opened SSMS and ran the agent job successfully. I'll wait to see what happens tomorrow morning at 09:30 when the job is scheduled to run again.
irb56Author Commented:
The job failed again this morning but this time for a different reason. It was complaining about an OLE DB Connector. I logged on to the server again and modified the package, creating a new OLE DB Connector. I saved and re-deployed the package, then scheduled the job to run at 10:00. The job duly failed. It's back to the old favourite... "No such interface supported" on the WinSCP Execute Process task.

There is something I can do to change this error. It involves changing the WinSCP script file. I've changed the UN/PW and IP details, but essentially the WinSCP script file looks like this:

option batch on
option confirm off
open sftp://Me:Password@192.168.1.1:22 -hostkey="ssh-rsa 1024 68:87:fa:79:2f:70:2c:28:f0:3f:a7:3b:9a:16:b9:00"
put D:\SSIS\DATA\ARCHIVE\Today\MyReport_*.zip
exit

When I edit the WinSCP script file and delete the -hostkey switch (so the line just reads "open sftp://Me:Password@192.168.1.1:22"), save and reschedule the agent job, there is a change of behaviour, although the end result is still a job failure. Firstly, the error message changes. The "No such interface supported" error disappears and is replaced by "The process exit code was "1" while the expected was "0"". Secondly, the WinSCP log file is updated, indicating that WinSCP has made a connection to the sftp server. The WinSCP log file reveals the problem:
"...
2011-03-29 10:17:38.644 The server's host key was not found in the cache. You have no guarantee that the server is the computer you think it is.
. 2011-03-29 10:17:38.645
. 2011-03-29 10:17:38.645 The server's rsa2 key fingerprint is:
. 2011-03-29 10:17:38.645 ssh-rsa 1024 68:87:fa:79:2f:70:2c:28:f0:3f:a7:3b:9a:16:b9:00
. 2011-03-29 10:17:38.645
. 2011-03-29 10:17:38.645 If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
. 2011-03-29 10:17:38.645
. 2011-03-29 10:17:38.645 Continue connecting and add host key to the cache? ()
. 2011-03-29 10:17:38.645 Attempt to close connection due to fatal exception:
* 2011-03-29 10:17:38.645 Host key wasn't verified!
. 2011-03-29 10:17:38.645 Closing connection.
. 2011-03-29 10:17:38.645 Sending special code: 12".

The problem is that I've been through this before, taking the hostkey switch in and out. I checked out the WinSCP site and with info I learned from there I tried a new approach. Firstly, using the WinSCP GUI, I changed the storage location of configuration data from the registry to an INI file. Secondly, I created a session profile called MySession, saving the password with this. I then made a connection, which prompted me to save the server's RSA fingerprint to the cache (which now stores it in the INI file rather than the registry). Thirdly, I changed the WinSCP script file again to now refer to the session profile as follows:

option batch on
option confirm off
open MySession
put D:\SSIS\DATA\ARCHIVE\Today\MyReport_*.zip
exit

I rescheduled the agent job for 10:37 and it worked! I've restored the schedule for 09:30 tomorrow and will keep my fingers crossed.

I'm hopeful this has now cracked the problem because I think the issue is probably due to the caching of the RSA fingerprint in the user registry rather than the INI file. As there are a couple of different user accounts that I use to log on to the server I suspect that the WinSCP configuration may be altered by logging on as different users and running SSIS package that invoke the WinSCP process. Hopefully the changes made have removed any user specific dependencies from WinSCP. I'll wait until tomorrow and hope to close the issue then.
AlexPaceCommented:
Yes if the server's key was stored under HKEY_CURRENT_USER then users won't have access to each other's settings.

You could verify this was the problem by installing the 30-day trial of Robo-FTP and using a command script like this:
WORKINGDIR "D:\SSIS\DATA\ARCHIVE\Today"
FTPLOGON "192.168.1.1" /servertype=SFTP /user="Me" /pw=Password /trust=allow
SENDFILE "MyReport_*.zip"
FTPLOGOFF
EXIT

Open in new window

That little /trust=allow option at the end of the FTPLOGON line tells Robo-FTP to just accept whatever SSH key the server offers as good, even if it is previously unknown.  Normally this is not a good security practice but it great for troubleshooting the specific problem you described above.

If you saved this command script in a file named test.s you could launch Robo-FTP and us command line switches to specify that it needs to run the script like this:

robo-ftp.exe -v -s"c:\the\command\script\path\test.s"
irb56Author Commented:
I'm pleased to say that the job worked just fine this morning (the first time a scheduled run at 09:30 has succeeded), so the trick of switching the WinSCP config storage from the registry to the INI file has done the trick. Now that it's working, I'm happy with WinSCP. If I get time later, I'll check out your suggestion regarding Robo-FTP.

Many thanks for your assistance.  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.