please feel free to edit the code i am not .Net expert...Thanks in advance... Variables 1) Input ---FTP paths 2) OutPut---Local Path 3) FtpFileList---Object variable for storing FTP paths VB Code: Imports System Imports System.Collections Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Class ScriptMain Public Sub Main() Try ' Get the ftp connection from the Connection Managers collection Dim ftpServer As ConnectionManager = Dts.Connections("FTP Connection Manager") ' Create a FTP connection object and us the credentials of connection manager Dim myFtpConnection As FtpClientConnection = New FtpClientConnection(ftpServer.AcquireConnection(Nothing)) ' Open the connection myFtpConnection.Connect() ' Set work folder with the value of the variable myFtpConnection.SetWorkingDirectory(Dts.Variables("Input").Value.ToString()) ' Create StringArrays for filenames and folders ' The folderNames aren't used, but is mandatory ' for the next method. Dim fileNames() As String Dim folderNames() As String 'Dim Filepath As String = Dts.Variables.Item("FileExists").Value.ToString() 'Get a directory listing and fill the StringArray variables myFtpConnection.GetListing(folderNames, fileNames) Dim fileNamesArray As ArrayList If Not (fileNames Is Nothing) Then ' Copy StringArray to ArrayList to fit in Object variable fileNamesArray = New ArrayList(fileNames) ' Optional sorter fileNamesArray.Sort() End If ' Fill ssis object variable Dts.Variables("FtpFileList").Value = fileNamesArray 'Download all files at once. Don't forget to add the SSIS variable Local DownloadDirectory to the ReadOnlyVariables myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) ' Close connection myFtpConnection.Close() ' Close Script Task, set result to success Dts.TaskResult = ScriptResults.Success Catch ex As Exception ' Fire error and set result to failure Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0) Dts.TaskResult = ScriptResults.Failure End Try End Sub End Class

Where is it failing?Chuck Pedretti | Magenic North Region | magenic.com

when there are no files in the ftp folder....its failing

If I had to guess I would say that this is the problem: ' Fill ssis object variable Dts.Variables("FtpFileList").Value = fileNamesArray 'Download all files at once. Don't forget to add the SSIS variable Local DownloadDirectory to the ReadOnlyVariables myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) Should probabally be this - so that you are not trying to receive files when none exist ' Fill ssis object variable Dts.Variables("FtpFileList").Value = fileNamesArray if fileNamesArray.Count >0 Then 'Download all files at once. Don't forget to add the SSIS variable Local DownloadDirectory to the ReadOnlyVariables myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) end if Chuck Pedretti | Magenic North Region | magenic.com

when there are no files in the ftp folder....its failing Just wow - I meant, which line of code is it failing on and what is the error messageChuck Pedretti | Magenic North Region | magenic.com

myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) You are calling the ReceiveFiles method of myFtpConnection with the fileNames variable as parameter. When fileNames is empty (Nothing) the method will still be called. Passing the empty fileNames object to the Method may cause an exception, which raises an errror in your Catch block. If this is the case you should have the error message in your log.

Chuck..i changed the code i am getting following error.. [FTP Script Task] Error: Error: Object reference not set to an instance of an object.

When fileNames is nothing, the array will not be initialized.

OK then change to this Imports System Imports System.Collections Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Class ScriptMain Public Sub Main() Try ' Get the ftp connection from the Connection Managers collection Dim ftpServer As ConnectionManager = Dts.Connections("FTP Connection Manager") ' Create a FTP connection object and us the credentials of connection manager Dim myFtpConnection As FtpClientConnection = New FtpClientConnection(ftpServer.AcquireConnection(Nothing)) ' Open the connection myFtpConnection.Connect() ' Set work folder with the value of the variable myFtpConnection.SetWorkingDirectory(Dts.Variables("Input").Value.ToString()) ' Create StringArrays for filenames and folders ' The folderNames aren't used, but is mandatory ' for the next method. Dim fileNames() As String Dim folderNames() As String 'Dim Filepath As String = Dts.Variables.Item("FileExists").Value.ToString() 'Get a directory listing and fill the StringArray variables myFtpConnection.GetListing(folderNames, fileNames) Dim fileNamesArray As ArrayList If Not (fileNames Is Nothing) Then ' Copy StringArray to ArrayList to fit in Object variable fileNamesArray = New ArrayList(fileNames) ' Optional sorter fileNamesArray.Sort() ' Fill ssis object variable Dts.Variables("FtpFileList").Value = fileNamesArray 'Download all files at once. Don't forget to add the SSIS variable Local DownloadDirectory to the ReadOnlyVariables myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) End If ' Close connection myFtpConnection.Close() ' Close Script Task, set result to success Dts.TaskResult = ScriptResults.Success Catch ex As Exception ' Fire error and set result to failure Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0) Dts.TaskResult = ScriptResults.Failure End Try End Sub End Class Chuck Pedretti | Magenic North Region | magenic.com

Remove this line of code... you're already downloading it the script. It's for the foreach loop ' Fill ssis object variable Dts.Variables("FtpFileList").Value = fileNamesArray Put this line of code within the "if not(filenames is nothing) Then" line 'Download all files at once. Don't forget to add the SSIS variable Local DownloadDirectory to the ReadOnlyVariables myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("Output").Value.ToString(), True, False) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Thanks to Joost and Chuck, both codes are working perfectly fine....

Joost, what changes i need to make if i want to delete the files in remote using same code...

Joost, what changes i need to make if i want to delete the files in remote using same code... Try something with myFtpConnection.DeleteFiles Method: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.ftpclientconnection.deletefiles.aspxPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter