Friday, July 8, 2011

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

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
        End If
            '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, "")          '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.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
            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.getlisting(folders/, Files)
                    If Not Files Is Nothing Then 'checking for no files
                        For Each FileName In Files
                    End If
            End If
            'Close ftp connection
            'Close writer
        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