none
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.

    Monday, March 07, 2011 6:12 PM

Answers

All replies

  • 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
    By: TwitterButtons.com
    Monday, March 07, 2011 6:25 PM
  • 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

    Monday, March 07, 2011 7:47 PM
  • 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??

     

    • Edited by Mimosa Arts Monday, March 07, 2011 9:53 PM edit
    Monday, March 07, 2011 9:49 PM
  • 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.

     

    • Marked as answer by Mimosa Arts Wednesday, March 09, 2011 6:14 PM
    Wednesday, March 09, 2011 6:13 PM
  • 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.

     

    Friday, April 01, 2011 6:13 PM