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


SSIS job: failure to connect to FTP server


SSIS job: failure to connect to FTP server

Author
Message
cafescott
cafescott
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 733
Hello,

I have an SSIS package that is calling the FTP script task. I can run the package from the package-store. Unfortunately, when I attempt to run it as a job I get the pretty common error: "Unable to connect to FTP server using 'FTP Connection Manager'."

I have been searching for solution. This is a pretty common problem and many people have offered answers. Unfortunately, I have not seen the solution that works for me. Here are a few of the pages that I've looked over:
http://www.sqlservercentral.com/Forums/Topic657509-148-1.aspx
http://www.sqlservercentral.com/Forums/Topic478084-148-1.aspx#bm485701
http://www.sqlservercentral.com/Forums/Topic278800-148-1.aspx

The package is reading the user name, password and server name from a table. (Not a config file and the settings are not hard-coded.) Prior to the FTP task, I have a script task where I execute this code:

FTPConnectionManager = Dts.Connections("FTP Connection Manager")
FTPConnectionManager.Properties("ServerName").SetValue(FTPConnectionManager, Dts.Variables("ServerName").Value)
FTPConnectionManager.Properties("ServerPort").SetValue(FTPConnectionManager, Dts.Variables("ServerPort").Value)
FTPConnectionManager.Properties("ServerUserName").SetValue(FTPConnectionManager, Dts.Variables("ServerUserName").Value)
FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables("ServerPassword").Value)

Both the "FTP Prep" task and the FTP task that follows it have "DelayValidation" set to true.

The package works beautifully when I right-click on the package and run it from the package store! The job is configured to run under a proxy using my credentials. That still doesn't enable me to get the FTP task to work.

Finally, I have tried to save the package using "ServerStorage" and this did not change anything. Also, I don't wish to apply a password to the package because it is going to be used by multiple people over time and I don't wish to have to use a password to open the package.

Does anyone have any ideas? Thanks!
cafescott
cafescott
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 733
I'm trying to imagine any single chore harder in this world of IT then getting the SSIS FTP task to work when run as a job. Why does Microsoft heap so much misery on us poor developers? They have money for examples, they have money for tutorials. This is extraordinarily painful!!!

I have been busy trying everything I can think of to get this work. I'm running in 32-bit mode. I have set the user name, server name, port, passive mode and password values in a config file that I load via Package Configurations. I've set a password on the package. I've specified the package protection level to "EncryptSensitiveWithPassord." I've set the MaxConcurrentExecutables to 10. Saved to my Integration Services server.

I've tried changing the command line so that it has the "Decrypt" option. I've added my package password after the 'Decrypt' switch, then copied the line to notepad. From there I typed it in exactly into a command window.

I get the same error every time: "Unable to connect to FTP server using 'FTP Connection Manager''. Thanks, Microsoft!!

Any morsel of help for this most laborious of tasks is appreciated. Thanks.
cafescott
cafescott
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 733
Well, I have a solution that works. I gave up on the FTP task. Instead, I create a batch file on the FTP server. I have shared out the folder where the FTP client resides (i.e., WinSCP). The batch file gets called by an Execute Process task in the package. The whole thing works as a job.

This didn't work for me before because I had failed to share out the folder where the FTP client was. Actually, I was trying to "pushd" into the FTP server, then run the batch file. I kept encountering errors with the "pushd" so just gave up that strategy.

The batch file resembles:
"\\myFTPServer\Putty\WinSCP.exe" /script = "\\myFTPServer\temp\Myscript.sc"

The script file resembles:
option batch on
option confirm off
open ftp://UserNameTongueassword@FTPServer:21
put "\\SomeNetworklocation\TheFile.csv"
close

My advice to SSIS developers trying to get the FTP to work as a job is give up on the FTP task and create a batch file.
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