Scenario: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
- Create SQL Server Table and Stored Procedure to insert Folder Name
- Create an SSIS Package from basics
- 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(
'FTP connection manager nameDim 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
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