Using SSIS Script Task to download files from FTP

Question

I am trying to download files from FTP using SSIS script task, I have written some code to download files from FTP using the ftpWebRequest class which is working. But I also need to take in to account that we might be asked to download it from a sFTP. Since many are suggesting that WinSCP assemblies are the best way to download sFTP files, can some provide me with some examples to download files using a SSIS script with the WinSCP classes.

PS: I am new to Vb.net

Note that if you end up downloading from an FTPS site, rather than sFTP, you could end up using the lightweight Alex FTPS Client — sorrell

developer__c developer__c · Accepted Answer · 2015-02-05T09:36:49

Yes, as people have said the only option is to use a library like WinSCP.

Take a look at the examples here on the WinSCP website - http://winscp.net/eng/docs/script_vbnet_robust_example

Here is the source code from the example

Imports System
Imports System.IO
Imports System.Diagnostics
Imports System.Xml
Imports System.Xml.XPath
Imports System.Configuration.ConfigurationManager

Public Class SFTP
    ' SFTP support, built on WinSCP
    Public Shared Function PutSFTP(ByRef filename As String, ByRef remotehost As String, ByRef username As String, ByRef password As String, _
                           Optional ByVal outfilename As String = Nothing, Optional ByVal output As String = Nothing,
                           Optional ByRef errmsg As String = Nothing) As Boolean

        ' Run hidden WinSCP process
        Dim winscp As Process = New Process()
        Dim logname As String = Path.ChangeExtension(Path.GetTempFileName, "xml")
        With winscp.StartInfo
            ' SFTPExecutable needs to be defined in app.config to point to winscp.com
            Try
                .FileName = AppSettings("SFTPExecutable")
                If .FileName Is Nothing OrElse .FileName.Length = 0 Then Throw (New Exception("from PutSFTP: SFTPExecutable not set in config file."))
            Catch ex As Exception
                errmsg = ex.Message
                Return False
            End Try
            .Arguments = "/xmllog=" + logname
            .UseShellExecute = False
            .RedirectStandardInput = True
            .RedirectStandardOutput = True
            .CreateNoWindow = True
        End With
        Try
            winscp.Start()
        Catch ex As Exception
            errmsg = "from PutSFTP:  Could not run the WinSCP executable " & winscp.StartInfo.FileName & Environment.NewLine & ex.Message
            Return False
        End Try

        ' Feed in the scripting commands
        With winscp.StandardInput
            .WriteLine("option batch abort")
            .WriteLine("option confirm off")
            .WriteLine("open sftp://" & username & ":" & password & "@" & remotehost & "/")
            If outfilename Is Nothing Then .WriteLine("put " & filename) Else .WriteLine("put " & filename & " """ & outfilename & """")
            .Close()
        End With
        If output IsNot Nothing Then output = winscp.StandardOutput.ReadToEnd()

        ' Wait until WinSCP finishes
        winscp.WaitForExit()

        ' Parse and interpret the XML log
        ' (Note that in case of fatal failure the log file may not exist at all)
        If Not File.Exists(logname) Then
            errmsg = "from PutSFTP:  The WinSCP executable appears to have crashed."
            Return False
        End If

        Dim log As XPathDocument = New XPathDocument(logname)
        Dim ns As XmlNamespaceManager = New XmlNamespaceManager(New NameTable())
        ns.AddNamespace("w", "http://winscp.net/schema/session/1.0")
        Dim nav As XPathNavigator = log.CreateNavigator()

        ' Success (0) or error?
        Dim status As Boolean = (winscp.ExitCode = 0)
        If Not status Then
            errmsg = "from PutSFTP:  There was an error transferring " & filename & "."
            ' See if there are any messages associated with the error
            For Each message As XPathNavigator In nav.Select("//w:message", ns)
                errmsg &= Environment.NewLine & message.Value
            Next message
        End If

        Try
            My.Computer.FileSystem.DeleteFile(logname)
        Catch ex As Exception
            ' at least we tried to clean up
        End Try

        Return status
    End Function
End Class

Using SSIS Script Task to download files from FTP

1 Answers