irb56
asked on
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\S
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.
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\S
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
put D:\SSIS\DATA\ARCHIVE\Today
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
. 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
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.
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
put D:\SSIS\DATA\ARCHIVE\Today
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
. 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
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.
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:
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:
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
EXITIf 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 "
ASKER
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. :-)
Many thanks for your assistance. :-)
ASKER