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

Scenario: 

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

Solution: 

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

Execute Stored Procedure in Script Task in SSIS Package - SSIS Tutorial

    Related Posts/Videos on Script Task    

• Project and Package Parameters - How to use Project Level Parameters and Package Level Parameters in Script Task in SSIS Package
• Script Task - How to use Variables in Script Task by using C# or VB NET Scripting Language
• How to display variable value from Script Task in SSIS Package
• How to use ReadOnlyVariables and ReadWriteVariables feature in Script Task
• How to Delete Top N Rows from Flat File Source