FTP Task will not work when scheduled as a job!

Question

FTP Task will not work when scheduled as a job!

steve maas

Old Hand

Points: 368
More actions
April 3, 2006 at 9:22 am

#167793

I have a job that runs a SQL script and creates a flat file. I use an FTP task to send the file to a specific FTP site. The job work beautifully when is is completed in the development environment.
After I build the package and deploy on the SQL server and set it up to run as a job it will not work on the SQL server. The error appears to be in the FTP connection. I get two separate errors:
'Unable to connect to FTP Server using FTP Connection Manager' and
'An Error Occured in the requested FTP operation. Detailed error description: The Password was not allowed'
I am sure something very simple is wrong.
Any thoughts.
Steve

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I would first try running it manually, possibly from VS2005 on the production server first. Be sure to check the FTP connection manager properties once it is there.

Was there a variable that you were supplying in the dev environment that you may have forgotten to set up in the job?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

steve maas Old Hand Points: 368 More actions · Answer 2

Thanks for your response. BTW. I do have a copy of your book and I have found it most helpful. Nice Work.

I have run this process manually in Visual Studio on the production server and it all works fine. I am not using any variables. I am completely stumped by this one.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

Usually problems like this relate to security -- the job running in a different account than running manually. But, it doesn't sound like it since it is the FTP part that is failing.

Jamie Thomson had another way to run jobs, don't know if it will help, but it is worth a try:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=270648

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

Oh, and I'm glad you like the book! Thanks!

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Coach James Ten Centuries Points: 1272 More actions · Answer 5

FTP can be tricky based on it's execution method, parent, permissions, security, and tasking.

One alternative method in providing standards and better security is to use a third party install like "SoftArtisans.FileUp". Their products can be used in many evironments with safe, easy, quick results.

Regards,

Coach James

mkbahrami SSC Veteran Points: 209 More actions · Answer 6

If you don't have a SSIS Package execution proxy user set up, you need to do so (create credential first using the correct win authent pwd); grant the proxy user perms in msdb dbase as 'sql agent user role'. This will replace the old 'job system' proxy user we used to create for non-sysadmins to run os-related jobs in SS2000. You don't need to give sysadmin permissions anymore. So now any job not owned by a sysadmin should default to run under your proxy user.

When a package runs fine from BIDS or from the filesystem using 'execute package utility', but not as a job, KB918760 helps figure it out. So my error said 'Unable to connect ot FTP server using "ftp connection manager" ', but the problem was the package decryption. In my case, I had to do 'save copy of package as' to SSIS PACKAGE STORE instead of to the file system, then store it under \MSDB\packagename and instead of 'do not encrypt sensitive data' or whatever you have now, choose 'rely on server storage for access control'. So now it will pay attention to the db roles you gave to your proxy user. tah dah!

HTH, Mary

emkeybee Valued Member Points: 62 More actions · Answer 7

I had similar problem with FTP. Your Idea worked thanks a lot.

Lance-398722 Valued Member Points: 57 More actions · Answer 8

MARY!

I suffered with this problem for a looong time now and you howed me light.

Thank you SO much!!

Erik Edvalson Valued Member Points: 50 More actions · Answer 9

Erik Edvalson

Valued Member

Points: 50

June 13, 2007 at 1:53 pm

#712746

I owe you one!!!! How did you figure this out?

Almino Tolentino Malaza Newbie Points: 9 More actions · Answer 10

MARY!!! Thank you so much. This time I have my SSIS working properly.

Hami Riahi Valued Member Points: 74 More actions · Answer 11

Hami Riahi

Valued Member

Points: 74

September 19, 2007 at 11:53 am

#734674

Thanks a lot! It helped me too.

subsonic-abercrombie Mr or Mrs. 500 Points: 520 More actions · Answer 12

Thanks for the heads up on this. I have a task I'm working where this WILL be an issue. Good to have work arounds.:D

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 13

This thread may have died, but I'll try anyway.

I'm having this same problem, but it also fails in SSMS. I am the dbo and sysAdmin, so granting permissions is not an issue. I'm using the 'Rely on Server storage roles for access control' already. What else am I missing?

Dan