SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to Send Files to FTP Server


Unable to Send Files to FTP Server

Author
Message
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
Hi,

Sorry if the topic already exists.
I want to process an excel report and send the file to FTP server.

I can receive file from the FTP server but unable to send it to it. But I can copy & paste the file to the location, so I think there is no permission issue.

After execution, I get the following error as

=======================================================================
[Connection manager "{E48D978F-8F62-42B6-9F06-DE3DD7444A5D}"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Switching to Binary mode. 200 PORT command successful. Consider using PASV. 553 Could not create file. .
========================================================================

I just cant understand the error and its root cause.


I use the following code in Script Task
---------------------------------------------------------------------------------------------
Try

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

cm.Properties("ServerName").SetValue(cm, Dts.Variables("ftpServerName").Value.ToString)

cm.Properties("ServerUserName").SetValue(cm, Dts.Variables("ftpUserName").Value.ToString)

cm.Properties("ServerPassword").SetValue(cm, Dts.Variables("ftpPassword").Value.ToString)

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

ftp.Connect()


Dim files(0) As String

files(0) = "C:\MTOrders.xls"

ftp.SendFiles(files, "//MTOrders.xls", True, False)

ftp.Close()

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

------------------------------------------------------------------------------------------------
Please help me in solving this issue.

Thanks in advance.

Priya

Regards
Priya
Jason Coleman
Jason  Coleman
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 192
Is there any reason why you are scripting this task and not using the FTP Task in SSIS?
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
Nothing specific. When Replaced Script task with the FTP Task
I get the following error

[FTP Task] Error: Unable to connect to FTP server using "FTP Connection Manager".

But the FTP test connection succeeded and fails when executing the package.

I use the remote path value as

"\Abc.xls"

Local path value is

"C:\FTPFiles\Abc.xls"

Should i want to change anything. Also I increased the Chunk size to 1000 KB.

Just cant recognize the root cause. Please help

Thanks in advance.

Priya

Regards
Priya
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
The same error occurs for the FTP task also

[Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Switching to Binary mode. 200 PORT command successful. Consider using PASV. 553 Could not create file. .

Regards
Priya
Jason Coleman
Jason  Coleman
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 192
By the rerror message it looks as if the server + firewall combination of the FTP server are rejecting the connection, not that there is anything wrong with your connection specs. The PORT command and PASV commands are used to open up communication for reading and writing data.

I would try connecting with an FTP client outside of your SSIS package to see what properties you need to set (I find Flash FXP good for this but there are lots of free ones on the mkt) and check the logs on the FTP server (if there are any) to see why the connection is not getting through.
You can also check firewall settings (if there is a firewall in between) to see if any ports are blocked, and what messages are being reported there.
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
Thanks again. If the firewalls are enabled, does it allow me to manually Copy & paste the same file?

I can Copy/Paste the same file to the FTP Server manually.

Can you please explain this possibility?

Also, Is there any possibility for me to check the FTP log?

I m connecting using ftp://abc.ftpserver.com/ with a username and a password.

Please help

Priya

Regards
Priya
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
Hi,

Thank u so much. I can upload now.

The problem is that "I shouldn't specify the remote file name. only "/" in remote file succeeds sending file to ftp server"

Thanks again.

Priya

Regards
Priya
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2351 Visits: 1403
Glad you solved your problem (and I learned from it) but what about the case where you want to specify another name for the destination file?
MSSQLBuddy
MSSQLBuddy
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 568
Hi,

Sorry for the late response.

When tried to change the FTP file name in command prompt using

send abc.xls Newabc.xls

This is successful in command prompt but gives me errors in the FTP task.

Priya....

Regards
Priya
ossied1
ossied1
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 1
I am having the same FTP error using a FTP task a part of a bigger ssis package. torubleshooting steps -
* Using an FTP client, I was successful
* Testing the SSIS FTP connection manager manually was successful
* Tried specifing my remote path through a variable and outside of a variable = failure

I have read this thread but I can't get my FTP task to work
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search