FTP files list to text file using SSIS.

I got a requirment to dump all the FTP file listing for all folders and subfolders to a text file.
I'm using here a free FTP server which you could use too
ftp://ftp.secureftp-test.com/            User:"test"    Password: "test")     
                
what SSIS FTP Task can you do nativly?

The FTP Task, supports, sending, receiving, deleting folders and directories, but doesn’t support watching for a specific files or getting a list of the FTP directory listing.

Get list of files and folders to a text file.

 A.Drag a script task 

B. Select “Microsoft Visual Basic 2008” as the scripting language, then click “Edit Script”

C. Add the following script

And the result will be at D:\FTPList.txt



Hope that helps!!

Download the package from http://www.box.net/shared/77tqeitp99czj6feefxv

Imports System

Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults

  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

    Public Sub Main()
        Dim FilePath As String = "D:\FTPList.txt"      'save to location - You can use a variable here.

        'delete if file exists, Comment out if you need to append to the existing TXT file

        If File.Exists(FilePath) Then
            File.Delete(FilePath)
        End If

        Try

            'Create the connection to the ftp server
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
            Dim Folders As String()
            Dim Files As String()
            Dim FileName As String
            Dim Folder As String
            Dim Dir As String

            'Set the properties like username & password

            cm.Properties("ServerName").SetValue(cm, "ftp://ftp.secureftp-test.com/")          'Server address
            cm.Properties("ServerUserName").SetValue(cm, "test")                        'user name
            cm.Properties("ServerPassword").SetValue(cm, "test")                        'password

            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            'Connects to the root of the ftp server,

            Dir = "/"                                'root directory
            ftp://ftp.connect/()
            ftp://ftp.setworkingdirectory(dir/)
            ftp://ftp.getlisting(folders/, Files)

            Dim SW As System.IO.StreamWriter  'Writer to write to the file

            SW = System.IO.File.AppendText(FilePath)

            'Write date time header (getting fancy)

            SW.WriteLine(Now().ToString + vbCrLf + vbCrLf + vbCrLf)

            'write root file list

            SW.WriteLine("Folder : " + Dir + vbCrLf + "----------------------" + vbCrLf)
            If Not Files Is Nothing Then  'checking for no files
                For Each FileName In Files
                    SW.WriteLine(FileName)
                Next
                SW.WriteLine(vbCrLf)
            End If

            'write other folders and their files' list

            If Not Folders Is Nothing Then 'checking for no other folders
                For Each Folder In Folders
                    SW.WriteLine("Folder : " + Folder + vbCrLf + "----------------------" + vbCrLf)
                    Dir = "/" + Folder
                    ftp://ftp.setworkingdirectory(dir/)
                    ftp://ftp.getlisting(folders/, Files)

                    If Not Files Is Nothing Then 'checking for no files

                        For Each FileName In Files
                            SW.WriteLine(FileName)
                        Next
                    End If
                    SW.WriteLine(vbCrLf)

                Next

            End If
            'Close ftp connection
            ftp://ftp.close/()
            'Close writer
            SW.Close()

        Catch ex As Exception 'Catch errors, make up your own error as you can see.

            Dts.Events.FireError(911, "", "Errrrrrrrrrrrrrrrrrror, something bad happened!!!", "", -1)
            Dts.TaskResult = ScriptResults.Failure 'reprot failure

        End Try

        Dts.TaskResult = ScriptResults.Success 'report success
    End Sub
End Class