SFTP in SSIS Package

Question

Was I dreaming or did I see a post that said the latest version of SSIS now supports SFTP. 

IF not I need to run a script in the script task that first creates the process:

Dim

 

winscp As Process = New Process()

winscp.StartInfo.FileName =

"winscp.com"

but I need to set up the connection etc within the script as I would do with the regular ftp script code.

Any good examples - the example on the winscp site is vaque.

I've now got the ftp task inside of a ForEachLoop container.  And I feed it a variable containing the next file in the directory which it now sends all the files in a directory.

Answer 1

There is no such SSIS version, but there are solutions.

First is your example to using the WinSCP:

http://winscp.net/eng/docs/guide_ssis

Second, there are commercial components with all the bells and whistles e.g:

1. Xceed: http://xceed.com/FTP_NET_Features.html
2. http://www.cozyroc.com/ssis/sftp-task

And a free one from CodePlex: http://ssissftp.codeplex.com/

---

Arthur My Blog

Answer 2

I'd like to echo Arthur.... I ended up writing a script wrapper around psftp.exe (PuTTY client: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html).

It's better to use a 3rd party task in this case, if your budget allows.

Regards,

Akim

Answer 3

Thanks,

I'm trying to use a hybrid solution.  Just for testing I've added an unlinked SSIS script task with the following code:

  ' Run hidden WinSCP process
            Dim winscp As Process = New Process()
            winscp.StartInfo.FileName = "C:\Program Files\WinSCP\winscp.com"
            'winscp.StartInfo.Arguments = "/log=" + logname
            winscp.StartInfo.UseShellExecute = False
            winscp.StartInfo.RedirectStandardInput = True
            winscp.StartInfo.RedirectStandardOutput = True
            winscp.StartInfo.CreateNoWindow = True
            winscp.Start()

            Dim ServerHostFingerPrint As String = "ssh-rsa 6666 fa:fb:fd:e6:9d:4d:3c:4d:ca:cc:44:dc:34:68:73:46"

(NOTE: Using the [] but the real username password and server are used in the task)

            Dim strSiteAddressFirstPart As String = "[username]:[password]@[IPServer]"
            Dim SessionAddress As String = strSiteAddressFirstPart + " -hostkey=\" + ServerHostFingerPrint            

Dim RemotePathDirectory As String = "\Upload"
            Dim FileNameToUpload As String = "C:\TEST OUTPUT\TEST.txt"

            ' Feed in the scripting commands
            winscp.StandardInput.WriteLine("option batch abort")
            winscp.StandardInput.WriteLine("option confirm off")
            winscp.StandardInput.WriteLine("open " + SessionAddress)
            winscp.StandardInput.WriteLine("cd /")
            winscp.StandardInput.WriteLine("put " & FileNameToUpload & " " & RemotePathDirectory)
            winscp.StandardInput.Close()

            ' Collect all output (not used in this example)
            Dim output As String = winscp.StandardOutput.ReadToEnd()

            ' Wait until WinSCP finishes
            winscp.WaitForExit()

Is this code actually running the SFTP transfer??

I run the task and it runs from yellow to green but the file never transfers.  What am i missing??

 

Answer 4

Went back to this fine article which fit better into the ForEachLoop architecture and uses WinSCP.

http://www.bidn.com/blogs/KeithHyer/bidn-blog/633/winscp-from-ssis-secure-ftp-transfers

written by Keith Hyer.

Works great after taking care of all the little gotchyas while plumbing it all together.  Ended up using a named stored session of the WinSCP. the explicit breakdown, although more flexible, never worked correctly.

All works grreat now.

Thanks for the ideas.

 

Answer 5

As a futher follow up.  I ended up taking the SFTP completely out of the SSIS package.  The pkg now graps data and writes out flat files only.  An hour after it runs to produce the files into a directory, I set off and Robo-FTP script which does all the SFTP heavy lifting. Robo-FTP is super.  It will even archive files sent and send an email to you if there's an error in the SFTP transfer.