February 7, 2013 at 7:20 am
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!
February 7, 2013 at 1:29 pm
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.
February 11, 2013 at 8:52 am
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://UserName:Password@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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply