Downloading Files from SFTP

Normally, if we want to download FTP files from SSIS, we have FTP task through which it can be easily done. But, if we have to download files from SFTP(Secured FTP), then the regular FTP files don’t work

We need a third-party tool to connect to SFTP. Normally winscp is used which is a Free Ware.

Usual Development Scenario:

Normally while coding, we open SFTP using winscp to check if all files are there. For the first time, when we open SFTP, it will prompt a message whether to cache it’ key. When we submit YES button, from then on it goes on smoothly.

Caching this host key is user specific. So when the SSIS package which connects to SFTP using this winscp, will be the same credential which had opened winscp manually. So there won’t be any issues while developing the ETL and the running the package using BIDS.

Problem:

Normally there will be an error when this SSIS which connects to SFTP is run as SQL job

Reason

Usually SQL job will run the job using a specific ID like “Service Account”. For this SQL Service Account, the host key for SFTP was not cached. So there will be an error as below

The server's host key was not found in the cache.

Along with this error, usually if debug we get even the host key which was missing

-----------------------------------------------------------------------------------------------

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. 
The server's dss key fingerprint is:  ssh-dss 1024 c1:70:cb:3b:27:16:10:ed:a5:c8:e5:ba:8f:1b:ec:ad 
If you trust this host, press Yes.
To connect without adding host key to the cache, press No.
To abandon the connection press Cancel. 
Continue connecting and add host key to the cache?  (Y)es, (N)o, C(a)ncel, (C)opy Key: Cancel  Host key wasn't verified!  Authentication failed.

 

Solution

There are 2 solutions for this

1.       Include the hostkey while connecting to SFTP as below

ConnectString = "open " + FtpType.ToLower() + "://" + Username + ":" + Password + "@" + Host + ":" + Port + " -hostkey=" + """ssh-dss 1024 c1:70:cb:3b:27:16:10:ed:a5:c8:e5:ba:8f:1b:ec:ad"""

Reference: http://winscp.net/eng/docs/scripting#hostkey

2.       Other option is to login to the system using SQL Service Account and connect to SFTP and cache the host key. This can be done only by the Windows IT team.