Hello,
I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.
Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.
I receive the same message for each of the files that was downloaded:
Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\this\is\my\path\datafile1.ext: No such file or directory.
The attempt to delete file "\usr\this\is\my\path\datafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.
With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.
Dan
Has anyone been able to remotly delete mutliple files? I'm convinced this isn't an ftp permission error as I can ftp using the same credentials in an ftp session and issue a del command to each of the files that is listed in the Output. Each one deletes.
I see there is one other unanswered post about a problem remotly deleting a file, but no answers.
|||
I am experiencing a similar problem and have not found a resolution. In my situation I am able to successfully delete files from Windows FTP servers but have not been able to delete files from Unix FTP servers. I know that this is not a permissions problem because I can go through any number of other FTP clients and delete files without a problem from both servers. I also have no problems downloading files from either the Windows or Unix FTP servers.
Any suggestions or other information would be welcome.
|||
I submitted a bug to the product feedback center. If you want to vote on it/add a comment to perhaps start getting a little more visability for this.
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=4cd7df40-bf26-41b0-835b-3369db79284b
I was able to find a workaround for my situation. I was also able to access the directory via a Samba share. So using the full UNC path to the directory I was able to use a Foreach file in directory loop with a FileSystemTask to delete the files. It runs pretty quick too. Obviously I can't compare the speed with the FTP task though.
As an FYI, my CIO had mentioned to me that he thinks he had the same problem with DTS. By the time I started working here I just adopted his custom script to perform the FTP so I never really tried it myself on DTS.
|||
Were you able to delete a single file using the FTP Task without using a wildcard?
Donald Farmer
|||
I setup a simple package with just the FTP Task and a variable to just one file (no wildcard).
SSIS package "Package.dtsx" starting.
Error: 0xC001602A at Package, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 \usr\my\path\tmp\test1.dts: No such file or directory.
The attempt to delete file "\usr\my\path\tmp\test1.dts" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.
.
Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".
Task failed: FTP Task
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
|||
I am getting exactly the same problem,
howerver this was part of my investigation of why I can FTP recieve files in development but the same task fails when run thru' sql agent.
any help on either will be appreciated.
Terry
|||
What is your error? How are you saving the connection information in the package (Package ProtectionLevel)? Are you using Configurations?
|||MS has closed this bug saying they cannot reproduce the error. If they require proof, I can provide them with a server and a SSIS package that reproduces this error. I have an FTP site that can be made available to them, and a SQL server with the SSIS package they can run.
Jarret|||
Well I hope they might consider your offer. I unfortunatly can't expose my ftp server to the internet. I really can't imagine how they arn't reproducing it unless they arn't using a UNIX server.
Out of curiousity, what is the OS of your server? Mine is running on SCO.
|||
In case anyone else runs into the situation where the FTP Task will not delete files from Unix FTP sites, I have been using the following as a work-around. I'm using it in a For Each loop to delete individual files on each pass, but it could be modified to delete wild-cards. The first step is to create a Script Task and set the read-only variables to the following (in my package most of these are coming from a Package Configurations file and are mapped to local variables):
FTP_file_name, FTP_server, FTP_root, FTP_port, FTP_username, FTP_password
Then use the following code within the Script Task:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Try
If FileExists(Dts.Variables("FTP_file_name").Value.ToString) Then
DeleteFile(Dts.Variables("FTP_file_name").Value.ToString)
End If
'Dts.Events.FireWarning(0, "Done", Dts.Variables("FTP_file_name").Value.ToString, String.Empty, 0)
Catch ex As Exception
Dts.Events.FireWarning(0, "Exception", "Message: " & ex.Message & vbCrLf & vbCrLf & "Stack: " & ex.StackTrace, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
Private Function FileExists(ByVal fileName As String) As Boolean
Dim retVal As Boolean = False
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName
Try
Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.GetFileSize, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
If fWebResponse.StatusCode = FtpStatusCode.FileStatus Then
retVal = True
End If
Catch ex As WebException
If CType(ex.Response, FtpWebResponse).StatusCode <> FtpStatusCode.ActionNotTakenFileUnavailable Then
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End If
End Try
Return retVal
End Function
Private Sub DeleteFile(ByVal fileName As String)
Dim remoteServer As String = Dts.Variables("FTP_server").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString + fileName
Try
Dim fWebRequest As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath)
Dim fWebResponse As FtpWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "DeletedFile " & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)
Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try
End Sub
Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest
Dim uBuilder As UriBuilder = New UriBuilder()
uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath
uBuilder.Port = DirectCast(Dts.Variables("FTP_port").Value, Int32)
Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)
fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)
Return fWebRequest
End Function
End Class
|||Has anyone from MS looked at this issue? I'm seeing it as well - seems like an error when attempting to delete multiple files via the FTP task.
[Connection manager "FTP"] Error: An error occurred in the requested FTP operation. Detailed error description: 550 \\results47747.zip: No such file or directory The attempt to delete file "\\results47747.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. 550 \\test_10_16_2006.zip: No such file or directory The attempt to delete file "\\test_10_16_2006.zip" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file. .|||
Still this script is not helping as its throwing exception back... Has anyone found other workaround please?
Regards,
paddy
|||Paddy -
I used a script fairly similar to the one above to work around this issue - what's the exception you're seeing?
Arjun|||
Hello Arjun,
I recieved the error 550 No such file or directory or do not have permissions but the same account works fine when used some client FTP software like SmartFTP and able to delete the files from there...
Thanks,
paddy
Ali Shahzad wrote:
Grant Mackay wrote: