How To Execute Stored Procedure In Script Task in SSIS Package ( Get FTP Folder Names) - SQL Server Integration Services (SSIS) Tutorial


We need to get folder name from FTP Sever Directory and Insert  them in SQL Server Table.


we will be using Script Task to perform this requirement. here are the things we will cover in this video

  1. Create SQL Server Table and Stored Procedure to insert Folder Name
  2. Create an SSIS Package from basics
  3. Use Script Task with FTP connection and ADO NET Connection, Get List of Folders from FTP Server and then execute Stored Procedure in Script Task to insert folder Name into table.

Public Sub Main()
        Dim StrFolderArrary As String()
        Dim StrFileArray As String()
        Dim folderName As String
        Dim RemotePath As String

        RemotePath = Dts.Variables("User::RemoteFolder").Value.ToString()

        Dim ADODBConnection As SqlClient.SqlConnection
        ADODBConnection = DirectCast(Dts.Connections("ADONET_TEST").AcquireConnection(
        Dts.Transaction), SqlClient.SqlConnection)
'FTP connection manager name
Dim cm As ConnectionManager = Dts.Connections("FTPConnection") 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 subfolder in the folder, strFile Arry will contain nothing, so close the connection. If StrFolderArrary Is Nothing Then ftp.Close() 'If folders are there, Loop through the strFolderArray arrary and insert into table Else For Each folderName In StrFolderArrary MessageBox.Show(folderName) Dim SQLCommandText As String SQLCommandText = "EXEC dbo.InsertFolderName '" + folderName + "'" MessageBox.Show(SQLCommandText) Dim cmdDatabase As SqlCommand = New SqlCommand(SQLCommandText, ADODBConnection) cmdDatabase.ExecuteNonQuery() Next ftp.Close() End If ' Add your code here ' Dts.TaskResult = ScriptResults.Success End Sub