FTP TASK PROBLEM

Dear all,

I have problem in my ftp task.For example,
I create two variable remotepath and localpath to set up remote and local path of ftp task. And the IsRemotePathVariable and IsLocalPathVariable is set to true.

I create two funtions to retrun the pathvalue. The return value

is like 'D:\A.TXT' , but in DTSX the varible value is changed to 'D:\\A.TXT'. So my ftp task fail.

How can I overcome this problem?

Thanks a lot.

Regards

wen

Replace all occurences of "\\" with "\" if its causing you a problem.

You can do this in an expression.

-Jamie

|||

It doesn't actually change the value. It is just that the undelying engine for SSIS is written in C and "\" is an escape character. To actually display the value of "\" it has to be preceeded by a "\". so to use/display it in a string it appears as "\\". For example \r actually means a carriage return.

As for your situation what error are you getting?

|||

when the path value is changed to "\\" , the error is as bellow,

Error: 0xC0029183 at FTP Task, FTP Task: File represented by "User::FTPPATH" does not exist.
Task failed: FTP Task

sql

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?sql

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

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

Dts.TaskResult = Dts.Results.Success
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 = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

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 Classsql

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

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

Dts.TaskResult = Dts.Results.Success
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 = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

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

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

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

Dts.TaskResult = Dts.Results.Success
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 = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

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

FTP Task - Delete remote files always fails

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

FTP Task - Delete remote files always fails

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

FTP Task - Delete remote files always fails

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

sql

FTP Task - Delete remote files always fails

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

FTP Task - Delete remote files always fails

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

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

sql

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.