Friday, June 24, 2011

FTP file and Folders list into a SQL Table

Today I came across a requirement to get all the files and folders list from a FTP server, The only way to get that will be through a script task that will loop through the files and get it into a SQL table.
Here's the question.
and here's my proposed solution....

I’ll be using a free test server ftp.secureftp-test.com User/Pass: test

We'll need to create a table to hold our results:

CREATE TABLE [dbo].[FTPList](
    [ListID] [int] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL ,
    [Folder] [varchar](100) NOT NULL,
    [FileName] [varchar](100) NOT NULL)

Now let's start....

•    Drag a “Script Task”.

•    2-edit the code as shown here:



Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
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()

        '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.secureftp-test.com") 'Serevr Name
        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 ftp server, to the root
        Dir = "/" 'Root directory
        ftp.Connect() 'Connect
        ftp.SetWorkingDirectory(Dir) 'Set working directory
        ftp.GetListing(Folders, Files) 'list fodlers and files

        Dim SQLStr As String    'SQL string to hold the root query
        Dim SQLStr1 As String   'SQL string to hold rest of the folders query
        Dim ConnString As String

        'SQL server connection to "Localhost" and my Database called "Admin"
        ConnString = "Data Source=localhost;Initial Catalog=Admin;Integrated Security=SSPI;"

        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open() 'open connection
        SQLCmd.Connection = SQLConn
        'write root file list
        If Not Files Is Nothing Then
            For Each FileName In Files
                'my table called "FTPList"
                SQLStr = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                SQLCmd.CommandText = SQLStr
                SQLCmd.ExecuteNonQuery()
            Next
        End If
        'write other folders and their files list
        If Not Folders Is Nothing Then
            For Each Folder In Folders
                Dir = "/" + Folder
                ftp.SetWorkingDirectory(Dir)
                ftp.GetListing(Folders, Files)

                If Not Files Is Nothing Then
                    For Each FileName In Files
                        SQLStr1 = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                        SQLCmd.CommandText = SQLStr1
                        SQLCmd.ExecuteNonQuery()
                    Next
                End If

            Next
        End If
        ftp.Close()
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class
 And the results will be at the FTPList table as shown:


That's it...Enjoy.......

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