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
The results...
Hope that helps someone...
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...
No comments:
Post a Comment