We have different files on FTP Server, we want to get the names of all the files from FTP Server folder and save to our SQL Server Table for Audit Purpose. By doing that multiple times we can get the snapshot of all the files available on FTP Server Folder.
This can be very useful when we are looking for some total files to be there before we start process etc.
we will learn following items in this video
- How to Create an SSIS Package from basics
- How to create FTP Connection in SSIS Package
- How to Create ADO.Net Connection in SSIS Package to Connect to Database
- How to Create variable for FTP Folder Name and pass to Script Task
- Write Script by using VB.Net in Script Task to Get the List of Files from a Remote FTP Folder and insert the file names to SQL Server Table.
- How to change the value of Variable to loop through different folder to extract file name without make any other change in our SSIS Package
Create SQL Server Table to Save File Names from FTP Server Folder
USE Test go Create Table dbo.FTPFileList (ID int identity(1,1), FolderName VARCHAR(100), FileName VARCHAR(200), LoadDate datetime default getdate())
Script Task PartIn this Import Part , you have to add below line
In Main Sub paste the below script
Dim StrFolderArrary As String() Dim StrFileArray As String() Dim fileName As String Dim RemotePath As String RemotePath = Dts.Variables("User::RemoteFolder").Value.ToString() Dim ADODBConnection As SqlClient.SqlConnection ADODBConnection = DirectCast(Dts.Connections("DBConnection").AcquireConnection
(Dts.Transaction), SqlClient.SqlConnection) Dim cm As ConnectionManager = Dts.Connections("FTP_Connection") 'FTP connection manager name Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) ftp.Connect() 'Connecting to FTP Server ftp.SetWorkingDirectory(RemotePath) 'Provide the Directory on which you are working on FTP Server ftp.GetListing(StrFolderArrary, StrFileArray) 'Get all the files and Folders List 'If there is no file in the folder, strFile Arry will contain nothing, so close the connection. If StrFileArray Is Nothing Then ftp.Close() 'If Files are there, Loop through the StrFileArray arrary and insert into table Else For Each fileName In StrFileArray 'MessageBox.Show(fileName) Dim SQLCommandText As String SQLCommandText = "Insert into dbo.FTPFileList (FolderName,FileName)
values ('" + RemotePath + "','" + fileName + "')" 'MessageBox.Show(SQLCommandText) Dim cmdDatabase As SqlCommand = New SqlCommand(SQLCommandText,ADODBConnection)
cmdDatabase.ExecuteNonQuery() Next ftp.Close() End If