Get File Names from FTP Server and Save to SQL Server Table in SSIS Package by using Script Task - SQL Server Integration Services (SSIS) Tutorial

Scenario: 


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
  1. How to Create an SSIS Package from basics
  2. How to create FTP Connection in SSIS Package
  3. How to Create ADO.Net Connection in SSIS Package to Connect to Database
  4. How to Create variable for FTP Folder Name and pass to Script Task
  5. 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.
  6. 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 Part

In this Import Part , you have to add below line

Imports System.Data.SqlClient

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




Get File Names from FTP Server Folder by using Script Task in SSIS Package

10 comments:

  1. Thank you so much for the video Sir. I was trying to do the same in my computer but when i copied the script task code and pasted to my script task of VS2017, it gives me errors. I am not sure if it is because of compatibility issue. Can you please suggest on this? thanks.

    ReplyDelete
  2. I get the below error:
    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  3. Thanks its great just have one question what if i want to add "modified data" from FTP

    ReplyDelete
  4. I get this below error.i had been check more then 5 time to find solution but i did not find out can you please help what is wrong with script( Script same copy past from blogs)
    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  5. I also get an error like the one above! Can you please run this script again, Can you replicate the script again, because I don't think it's working anymore..

    ReplyDelete
  6. Script isn't working, same error as other users above

    ReplyDelete
  7. Yes i would like to find out a solution. I have been a fan of this site for a while now. Not sure why we are all getting this same error that is shown above?

    ReplyDelete
  8. saw a post somewhere stating that using WinSCP you need to add this code. i can't get this to work either. ugh.

    public class example
    {
    static ScriptMain()
    {
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
    }

    static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
    if (args.Name.Contains("WinSCPnet"))
    {
    string path = @"Path to DLL";
    return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
    }
    return null;
    }
    public void Main()
    { can now use DLL things in here}
    }

    ReplyDelete
  9. So i battled with this for a bit and for me i got it working by in the script adding a connection string like this. i have the two lines of code commented out from above and just added this:

    'Dim ADODBConnection As SqlClient.SqlConnection
    'ADODBConnection = DirectCast(Dts.Connections("DBConnection").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

    Dim ADODBConnection As New SqlClient.SqlConnection
    ADODBConnection.ConnectionString = "Data Source = 10.xxx.x.xxx;Initial Catalog=ONBOARDING;User ID=tprssss;Password= xxxxxxxx"
    ADODBConnection.Open()

    i hope this helps someone.

    ReplyDelete
  10. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. https://www.buyyoutubesubscribers.in/

    ReplyDelete