SSIS Synchronize Remote FTP Directory

Option Strict Off

Imports System
Imports System.Collections
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

 Public Sub Main()
 'Author: Tommy Bollhofer (tbollhofer2@gmail.com, http://www.linkedin.com/in/tommybollhofer)
 'Last Modified: 09/0/2008
 'Purpose: Get a distinct list of file names from a table into an ArrayList and compare against a remote FTP server. 
 'Retrieve only the files that have not already been processed into the database. 
 'See comments for important parameter modifications.

 Try
 ' FTP Connection
 Dim fCM As ConnectionManager = Dts.Connections.Add("FTP")
 fCM.Properties("ServerName").SetValue(fCM, "ftp.sqlservercentral.com") 'Modify this line to reflect the name of the FTP server
 fCM.Properties("ServerUserName").SetValue(fCM, "sqlserver") 'Modify this line to reflect the user name
 fCM.Properties("ServerPassword").SetValue(fCM, "password") 'Modify this line to reflect the password
 fCM.Properties("ServerPort").SetValue(fCM, "21")
 fCM.Properties("Timeout").SetValue(fCM, "0") 'No TimeOut
 fCM.Properties("ChunkSize").SetValue(fCM, "1000") '1000 kb
 fCM.Properties("Retries").SetValue(fCM, "1")
 Dim ftp As FtpClientConnection = New FtpClientConnection(fCM.AcquireConnection(Nothing))

 ' OLEDB Connection
 Dim dCM As New OleDb.OleDbConnection(Dts.Connections.Item("OLE_DST").ConnectionString) 'Modify this line to reflect the name of the OLEDB connection manager object
 Dim sqlCommand As New OleDb.OleDbCommand("SELECT DISTINCT FileName + '.gz' AS FileName FROM FTPSchema.MyTable WITH(NOLOCK)", dCM) 'Modify the query for your environment

 dCM.Open()
 Dim sqlResult As OleDb.OleDbDataReader
 Dim sqlFileNames As New ArrayList
 sqlResult = sqlCommand.ExecuteReader()
 While sqlResult.Read()
 sqlFileNames.Add(sqlResult.GetString(0))
 End While
 sqlResult.Close()

 'Uncomment for Debug
 'System.Windows.Forms.MessageBox.Show("Before the File List")

 Dim ftpFileNames() As String
 Dim ftpFolderNames() As String

 ftp.Connect()
 ftp.SetWorkingDirectory("/logs") 'Modify this line to reflect the remote FTP working directory
 ftp.GetListing(ftpFolderNames, ftpFileNames)

 'Uncomment for Debug
 'System.Windows.Forms.MessageBox.Show("After the File List")
 'System.Windows.Forms.MessageBox.Show(ftpFileNames.GetUpperBound(0).ToString)

 Dim FilesToGetAL As New ArrayList

 Dim i As Integer
 For i = 0 To ftpFileNames.GetUpperBound(0)
 If sqlFileNames.IndexOf(ftpFileNames(i)) = -1 Then
 FilesToGetAL.Add(ftpFileNames(i))
 End If
 'Uncomment for Debug
 'System.Windows.Forms.MessageBox.Show(ftpFileNames(i).ToString)
 Next i

 'Uncomment for Debug
 'System.Windows.Forms.MessageBox.Show("After Loop")

 Dim FilestoGet(sqlFileNames.Count) As String
 FilestoGet = FilesToGetAL.ToArray(GetType(String))

 ftp.ReceiveFiles(FilestoGet, Dts.Variables("varFileDirectory").Value.ToString, True, False)
 ftp.Close()

 Catch ex As Exception
 Dts.TaskResult = Dts.Results.Failure
 End Try
 Dts.TaskResult = Dts.Results.Success
 End Sub

End Class