Is SFTP possible in SSIS package without using WinSCP

Question

I am having a SSIS package which does FTP. Now i need to change this to do SFTP, is there a possibility i can use FTP Task and do SFTP, or do SFTP without using WinSCP.

Answer 1

Not out of the box. There are other tasks available you can use any SFTP library in script, or use an SFTP powershell command. Paid components are generally better as most of the SFTP tasks you'll find seem a bit ... dated and the UI is a bit clunky.

For starters, you could check SSIS SFTP Task Component. It depends on SharpSSH, a library that hasn't been updated since 2013

On the other hand, Posh-SSH and the underlying library, SSH.NET had a release only a few weeks ago.

In my case, I ended up using Posh-SSH with a PowerShell script because it allowed me to compare the directory listings of remote and local files, detect the new files with standard PowerShell operations and download only the newer/changed files using SFTP.

For example, the following Powershell function will retrieve both listings and compare them using the Compare-Object command:

function get-diffs( $session,$remotePath,$localPath)
{
    $remoteFiles=Get-SFTPChildItem -SFTPSession $session -Path $remotePath 
    $localFiles=Get-ChildItem $localPath

    Compare-Object -ReferenceObject $localFiles -DifferenceObject $remoteFiles -Property "Name" -PassThru
}

The result contains a SideIndicator property that shows which files have to be moved from one side to another. Eg:

$newFiles=$diffs|where SideIndicator -eq '=>'
...
foreach ($f in $newFiles)
{
    Get-SFTPFile -SFTPSession $session -RemoteFile $f.FullName -LocalPath $dropFolder -Overwrite
}

Doing the same thing with SSIS would be a lot harder.