Get a folder's file list to SQL table using VB.net

A Recent challenge about how to get the folder's list of files int oa SQL table for file comparision.

I create a table called "FileList" under my test database called "Admin"

Use [Admin]
GO

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

I'll use the below script in a script task in SSIS.

Make sure to change the scripting language to Visual Basic 2008

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.SqlClient
<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 SQLStr As String    'SQL string to hold the root 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
        Dim di As New IO.DirectoryInfo("c:\")
        Dim fi As IO.FileInfo() = di.GetFiles()
        Dim f As IO.FileInfo
        'list the names of all files in the specified directory
        For Each f In fi
            SQLStr = "INSERT into FileList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + di.ToString + "','" + f.ToString.Trim + "')"
            SQLCmd.CommandText = SQLStr
            SQLCmd.ExecuteNonQuery()
        Next
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

The results...

 Hope that helps someone...