ftp task to network drive?

Hi I have a simple ftp task that downloads some files from an ftp to a local directory. That package run as a sheduled job from the sql 2005 server without a problem so far.

Now I whant to have the same task but to download the files to different mapped network drives , so I mapped the drives then I changed the paths to the config file but since then the job fails...

is it possible to do that or I did something wrong?

Thanks
g

Drive mappings are local to the user profile, so trying to use them in unattended jobs which run under a service account context does not make too much sense. I would strongly advise you to use a UNC path instead, e.g. \\Server\Share\Folder\File.ext

You will need top ensure that the service account or job proxy account has access to the share, as well as the normal folder permissions required for local as well.

|||Hi Darren,

Thanks a lot for the reply!!

I try the UCN path from the BI studio and it works fine but since I run it as a job faild. It must be something with the access to the share as you already said but since I am a newbie I don't know where exaclly to look for the problem... I am not running the job from a proxy do I have to create one? And if yes could you let me know for the process ?

As about the folder permissions I ve set full permission to "Everyone".

Thanks in advanced
G
|||

There is no need for a proxy account. By default your job will run under the context of the SQL Server Agent service account. Make sure you know what this is, and then check if that account has access to the share.

FTP Task failure in SQL Job

I have a SQL IS package, with FTP task. It runs fine when I am running from the client. When deployed it on the server as a SQL Job, it failes with error message

"Unable to connect to FTP server using 'FTP Connection Manager' (Name I used for the FTP connection in IS package)".

I tried to run the .dtsx on the server connecting to Intergration services. And I get same error message with little more details, "Password was not allowed".

With same USER_ID and Password and default port number I am able to connect to the FTP site with no problems.

Do I need to do it differntly making the FTP connection inside the IS package? What's the correct way of doing it ?

satya

When you deploy your package to SQL Server make sure you use Protection Level: "Rely on server storage and roles for access control"

|||Thanks for responding to my questions. I did what you suggessted and am still having the same issue.

Steps I followed:

1. Copy the .dtsx to server location from my local.
2. Imported the package to SQL server location with the suggested options.

Do you think, I am missing any thing still ?|||

Yes.
It gets corrupted when you copy.

You can either:
1. Deploy without copy

or

1. Set protection level to EncryptSensitiveWithPassword
2. Copy
3. Imported the package to SQL server location with options I suggested before.

FTP Task and Temporary Internet Files

I have created a package within SQL Server SSIS which includes an FTP Task, deployed it to our SQL Server (2005 SP1) msdb database and am running this job under SQL Agent on Windows Server 2003. Due to company security requirements this job has to be run under a service account within SQL Agent. The problem with this is that even though a directory is specified within the FTP Task to place any downloaded files into, the files are first written to the TIF (Temporary Internet Files) directory of "Default User" which is on the system drive. Based on corporate standards the system drive (C:) on our servers are only configured with enough space for the OS and other system files. All of the files being transferred are compressed, but some are still well over 1GB in size. The result is that many of our downloads are failing due to the system drive running out of space.

I have attempted to run IE by using "Run As" with the service account credentials, and have changed the location of the TIFs to a different drive, rebooted and verified the settings. When the SQL Agent job was run again, the files were still being written to the "Default User" directory on the system drive. I also created a new template account with the TIFs pointing to a non-system drive and used the User Profiles functionality of System Properties to copy the new template account to "Default User", but still the files are being written to the system drive.

My questions are:

is there a way to stop the FTP Task from using TIF (i.e. just directly write the file to the location specified) is there a best practice around how to setup a service account and have it create a proper user profile that can be managed separate from "Default User" short of specifying during the OS install, is there a way to move the "Default User" profile directory to a different drive

In case anyone else runs into this situation, I thought I would describe what ended up happening.

The page file was moved off of the system drive; otherwise the server was locking up when the system drive ran out or space. Now that the page file is on a separate drive, once the system drive runs completely out of space during a download the FTP Task (or whatever is managing the FTP transfer in the background) just starts writing the file being downloaded to the specified location (apparently it's smart enough to stop caching without crashing once there is no space left). Once the file that filled up the system drive completes downloading then it is removed from the TIF; normally the files aren't removed from the TIF until the entire package finishes execution.

The only downside to this is that the time period between the system drive becoming full and the file download completing, there are difficulties with logging into the machine. The difficulty is that some error messages are displayed and not being able to use your user profile - the Default User profile is used instead.

|||I am experiencing a similar problem. Does anybody out there know how to address this issue please?

Chris Lund, Software Installation Limited, Manchester UKsql

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 file using DTS package

I have a DTS package which does the following
1. EXECUTE SQL TASK (query in it)
on success
2. Microsoft OLE DB (database connection)
transform data task
3. Text File Destination (creates a text file TEST in a folder)
on success
4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
The problem is I click on the FILE TRANSFER PROTOCOL TASK
and its asks me to enter the FTP SITE USERNAME AND PASSWORD
and then the destination DIRECTORY PATH.
I don't know the path. What do I need to mention here
Also when I click on file and select a Blank Text file from there
and check overwrite still after the task is complete data does not
appears in that.
ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
Directory path BLANK.
Does anyone knows how to figure this out. I am sure its a simple
solution.VJ:
What do you mean you do not know the path? If you were to manually upload
the file to an ftp site you would type in the ftp site. For example:
ftp.access.com. If this was a valid ftp site then I would be taken to the
folder where I needed to send my files. This is usually a setting that is
set up by the company in charge of the ftp site.
So when you set up the FTP on DTS you would chose internet as the source,
enter your username and password. On the path you would use :ftp.access.com.
I would need to know more information on your file move or overwrite
problem. Does the problem exist when you move the file from your file that
you created to the ftp site? Maybe once you get the FTP path configured
correctly you will not have this problem.
--
Thanks Kllyj64
"VJ" wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>|||VJ wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>
Hi
I think the problem is that the FTP task is only only able to download
files from an FTP site - not upload.
Instead you can create a bat file with the commands for the files you
want to upload and then execute this bat file from your DTS package.
That's the way I'm doing it and that works fine.
Regards
Steen Schlüter Persson
DBAsql

FTP file using DTS package

I have a DTS package which does the following
1. EXECUTE SQL TASK (query in it)
on success
2. Microsoft OLE DB (database connection)
transform data task
3. Text File Destination (creates a text file TEST in a folder)
on success
4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
The problem is I click on the FILE TRANSFER PROTOCOL TASK
and its asks me to enter the FTP SITE USERNAME AND PASSWORD
and then the destination DIRECTORY PATH.
I don't know the path. What do I need to mention here
Also when I click on file and select a Blank Text file from there
and check overwrite still after the task is complete data does not
appears in that.
ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
Directory path BLANK.
Does anyone knows how to figure this out. I am sure its a simple
solution.VJ:
What do you mean you do not know the path? If you were to manually upload
the file to an ftp site you would type in the ftp site. For example:
ftp.access.com. If this was a valid ftp site then I would be taken to the
folder where I needed to send my files. This is usually a setting that is
set up by the company in charge of the ftp site.
So when you set up the FTP on DTS you would chose internet as the source,
enter your username and password. On the path you would use :ftp.access.com
.
I would need to know more information on your file move or overwrite
problem. Does the problem exist when you move the file from your file that
you created to the ftp site? Maybe once you get the FTP path configured
correctly you will not have this problem.
--
Thanks Kllyj64
"VJ" wrote:

> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>|||VJ wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>
Hi
I think the problem is that the FTP task is only only able to download
files from an FTP site - not upload.
Instead you can create a bat file with the commands for the files you
want to upload and then execute this bat file from your DTS package.
That's the way I'm doing it and that works fine.
Regards
Steen Schlter Persson
DBA

FTP Delete task in SQL agent

I have a FTP task created using SSIS that need to delete lot of files in a directory. This task is working fine if I'm Executing the SSIS package manualy but not working if I'm scheduling the SSIS package in SQL agent job.
Anybody have faced this kind if situation is there any solution for this?.

have you gotten a resolution on this problem? I am having the same issue on a FTP send task and it works fine when I run it manually but it bombs out when I schedule it.

Let me know... Thanks

|||

Error messages really help in these situations guys.

-Jamie

|||

I actually found the answer for my problem here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356558&SiteID=1

I just made the protection level to "DontSaveSensitive" and when the SQL Server agent ran the package it ran properly.

Hope it helps :)

FTP Connection problem with "The Password was not allowed" error message

Hi

I have a simple FTP task that I am trying to connect to an FTP server. I can test the connection fine, however, when I try and execute the package from Visual Studio 2005 I get the "The Password was not allowed" message. I have found some threads that mention setting the protection level on the package to EncryptSensitiveWithPassword, however, I still get the same message appearing.

Has anyone any idea as to what is causing this and how I can get around it

Thanks

Darrell

The error comes from the FTP server due to a invalid password. Can you tell me what is the value ProtectionLevel property in the Package?

Thanks,

Ovidiu Burlacu

|||EncryptSensitiveWithPassword means you have to enter a password in the package properties. You have specified the wrong password there, most likely.

For running in BIDS, EncryptSensitiveWithUserKey is just fine.|||

I had set the PackageProtection to EncryptSensitiveWithPassword, but that didn't work either.

As for an incorrect password, this can't be correct, because when I test the FTP connection it works fine

Any other thoughts ?

Thanks

D

FTP connection only works in BIDS

I have an FTP task that will only work when running in BIDS.

When trying to run as a package on the server or calling the package from a job, I get the following error in the log file:

OnError,<servername>,<user>,FTP Task,{B2F5BB68-C6F8-4EE5-ABC0-71C3636E3E4A},{B7B41A88-18DD-4AD7-8CDE-9E0C1B74DA02},6/26/2007 12:09:11 PM,6/26/2007 12:09:11 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

When running in BIDS it is fine.

Any know what is causing this?

Can you access the ftp site when RDP'd onto the server? My guess it is blocked.|||

we tried that

we used the old cmd file that was being called by the 'execute' task and it works fine on the server

can connect to ftp, can login, can transfer file, can rename

|||

When trying the command file, was that from a job -

- created in the same was as the SSIS job

- owned by the same SQL user

- and using the same proxy account if one is used at all?

Those points would effect the security context used to host the process within Windows, which in turn may impact credentials passed to the FTP service, or even just having network access or proxy access.

|||

It is because the security level was wiping the passwords when it was deployed.

We have now set it to use a password and it works ok.

|||

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

|||

Reji George wrote:

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

There really isn't a set "format." You just fill in the blanks for server, user name, password, etc...

Did you take note of the Package ProtectionLevel property message above?

FTP authentication information does not persist when deploying packages

I am trying to deploy a SSIS package which includes a FTP task. It works fine on the machine it was developed . When deploying the package on a production server, all other config changes work, but the FTP task is failing, with authentication error.

any help on how to persist the data would be appreciated

thanks

Maybe it's because the password was stored encrypted and couldn't be decrypted on the new machine... This is quite common and happens often when moving a package from machine to machine.

As far as I know you can get around this by using package passwords instead of the normal encryption. You can also save a package without encryption (what is not recommended...) or use a configuration to push the password back into the package i.e. using an XML file (but then the password is unencrypted in the XML)...

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

sql

from AdventureWorks to AdventureWorksDW

AdventureWorks and AdventureWorksDW are good samples for transaction and dw.

Is there any sample of SSIS package to load data from AdventureWorks to AdventureWorksDW? For example, I'd like to update AdventureWorksDW db everyday from transaction db.

How about if some dimensions changed, like employees, and productions?

Thanks!

-Z

Project REAL has examples.

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx|||

Phil Brammer wrote:

Project REAL has examples.

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

Phil,

Does it have examples of using AdventureWorks though? I didn't think so. I admit I haven't looked at the Project REALstuff in too much detail.

-Jamie

|||Z,
Project REAL doesn't go against AWDW, but the concepts would be the same...

Another good resource (again not specific to AdventureWorks) is the Ralph Kimball Group Webcast.

Here are some tutorials (have you looked at MSDN at all?) from Microsoft that are against AWDW.|||

Hi, Phil,

Thanks much for your quick response. It helps a lot!

BTW,

Do you happen to know if there is any sample for the daily changes from DW to SSAS?

-Z

|||

ZZhang wrote:

BTW,

Do you happen to know if there is any sample for the daily changes from DW to SSAS?

-Z

That I cannot help with! That's out of my knowledge area!|||I am looking for advetureworkdb to advetureworkdw plus full digram of advetureworkdw

from AdventureWorks to AdventureWorksDW

AdventureWorks and AdventureWorksDW are good samples for transaction and dw.

Is there any sample of SSIS package to load data from AdventureWorks to AdventureWorksDW? For example, I'd like to update AdventureWorksDW db everyday from transaction db.

How about if some dimensions changed, like employees, and productions?

Thanks!

-Z

Project REAL has examples.

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx|||

Phil Brammer wrote:

Project REAL has examples.

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

Phil,

Does it have examples of using AdventureWorks though? I didn't think so. I admit I haven't looked at the Project REALstuff in too much detail.

-Jamie

|||Z,
Project REAL doesn't go against AWDW, but the concepts would be the same...

Another good resource (again not specific to AdventureWorks) is the Ralph Kimball Group Webcast.

Here are some tutorials (have you looked at MSDN at all?) from Microsoft that are against AWDW.|||

Hi, Phil,

Thanks much for your quick response. It helps a lot!

BTW,

Do you happen to know if there is any sample for the daily changes from DW to SSAS?

-Z

|||

ZZhang wrote:

BTW,

Do you happen to know if there is any sample for the daily changes from DW to SSAS?

-Z

That I cannot help with! That's out of my knowledge area!|||I am looking for advetureworkdb to advetureworkdw plus full digram of advetureworkdw

Free CR w/VS6 Enterprise

The folder info is:

Crystal Reports is shipped and installed as part of
the VB installation package except in VB6. This can
be found in \common\tools\crysrep
double click on crystl32.exe to install.Just be aware of the License Agreement (http://msdn2.microsoft.com/en-us/library/aa288179(vs.71).aspx)

4.2 You may not use the Software or a Server/Web Application on a rental or timesharing basis or to operate a service bureau facility for the benefit of third-parties unless you first acquire an Application Service Provider License from Crystal Decisions. The Runtime Software may be installed on only one server in each Server Environment, and only one instance of the Runtime Software per Server/Web Application may be utilized on that Server at any one time.

4.3 This license agreement does not in itself give you any right to distribute Server/Web Applications to third parties.

There are other restrictions (see the link), but these are the two that most often come into play.|||oddly enough, I couldn't find it in the Tools folder.

Seems to me that I installed it on the old machine, but never used it.