Unable to connect to FTP server using "FTP Connection Manager"

Question

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log gives no other detail...):

"Unable to connect to FTP server using "FTP Connection Manager"

I've tried setting up the SSIS package with the ProtectionLevel of EncryptSensitiveWithPassword. Going into the SQL job and entering the password. All the tasks run fine except for the FTP task.

I then tried setting up the SSIS package with the ProtectionLevel of DontSaveSensitive. I then setup a configuration file. Manually entered the password into the DTSConfig file. Chose the configuration file in the configurations tab of the step setup. But I'm still getting the same error when running the job.

The package runs fine when ran in visual studio.

Any ideas?

Answer 1

First make sure that you are able to connect FTP server through windows explorer, and then try three things

For Package:
1- Package Protection Level= EncryptSensitiveWithUserKey

For FTP Connection Manager:
2-Server Port= 21
3-UsePassiveMode= False

Answer 2

The firewall wasn't set up correctly. Once I got the network admin to let port 20 through, all was working well.

I ended up sticking with the "DontSaveSensitive" and using a config file to make deployment easier through multiple environments.