SSIS Package - FTP Task fails when run from a job

Question

SSIS Package - FTP Task fails when run from a job

HookSQLDBA7

SSChampion

Points: 10709
More actions
April 3, 2014 at 7:27 am

#290001

We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. I have a SSIS Package which connects to a FTP Site and downloads a file. Then it truncates a table and loads the file data into a table. This package works okay when executed from within VSS and SSMS (In SSISDB, right click on the package and execute). However, when I execute it as a Job it does not run and appears to be failing on the first task which is the FTP Task.
SQL job step - Type: SSIS Package; Run as: SQL Server Agent Service Account (domain account called playuser); Authentication: Windows Authentication
In the All Executions Standard Report for the SSISDB Catalog, it only says: FTP Download File: Errors: There were errors during task validation.
Is this because my domain account does not have access to the FTP Site?
Is this where I need to come up with a Proxy Account with Credentials?
Do I need to set up a SQL Server Login (Proxy Account) with the same username being used in the FTP Batch file?
FTP Commands in a batch file:
username
password
cd omb
asc
get STRMASTER
quit
I am still researching.
Thanks, Kevin

Viewing 4 posts - 1 through 4 (of 4 total)

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

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 1

I believe this is an issue with your account versus the "proxy" account. This is a very common issue....

Verify that the proxy account can access the FTP site using the same credentials you are using in your SSIS task (find the command prompt program, press the shift-key, right-click and choose run as different user)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Could you post the error message in the Job History ?

--

SQLBuddy

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 3

Thanks everyone. I created the proxy account with credentials and it worked. It worked when using my windows login as the IDENTITY. When I tried to use the windows login that is used to start the sql agent service, it did not work. So, I think I will need to create a new windows user with the same abilities as my windows login, with a non-expiring password,

create the credentials and proxy for the new windows user, and set that user as the 'Run As: ' value within the job step.

Thanks, Kevin