FTP Taks Fail

FTP Taks Fail

• SergioTorres

  Apologies if i'v hijacked this thread but I need to get this sorted ASAP.

  Craig,

  in answer to above :

  There is no choice for Run As: the only option is "SQL Agent Service Account"

  I have tried running the step as Operating System (CmdExec) and it still fails with :

  Step 0:

  Message
  The job failed. The Job was invoked by User MyDomain\Administrator. The last step to run was step 1 (oisload).

  Step 1:

  Message
  Executed as user: MYSERVER\SYSTEM. The process could not be created for step 1 of job 0x0A5BB67ABFE70E46A40DEAE5E82E8D30 (reason: 2). The step failed.

  I'v check the FTP server logs and there are no entries for this connection.

  I have created a BAT file to do the same FTP and it runs OK in Agent.

  Terry

  
  

• nickicha

  Apologies for the delay, but perhaps it was good. Rethinking this, I suspect I was missing the easy/obvious issue. I think I asked earlier in the thread wha your package protectionlevel property is The default is "EncryptSensitiveWithUserKey" which means properties that are marked 'sensitive' such as username, passwords, server names...will be encrypted when the package is saved use a key comprised of the current user and machine. Therefore when you load the package as a different user (e.g. AgentService Account) the package will open and execute BUT the sensitive fields will get stripped out, which means the FTP task is trying to connect to your server with no credentials and thus why the server will not connect.

  You should read in BOL about he packageprotectionlevel property as you likely want to change it to something else and/OR investigage creating an agent Proxy account that you assign to the SSIS job. For example I can create an Agent proxy account, which uses a 'credential' and that credential can be for the same domain\user that you execute as when in design environment. You then (within the Agent UI) assign the proxy account to the SSIS job step and then package will execute ok. Thats just an example, you may not want to use the same account for production. Everyone's situation is different which is why you want to read up on SSIS protection levels and Agent proxy accounts.

  Hope that helps and apologies again for the delay.
  -Craig

  
  
  

• tc1109

  Hi, Craig,

  l've tried to run within the designer with 'start without debugging'. The package run successfully. It have the error only when l run the job from the SQL agent. l can also run the FTP from my OS using a batch job......So what're the other possibilities so that l could try out!

  Thanks..

  
  

• E_l_i

  We are talking about agent on the same machine as your development right

  In agent job step, what did you select for "Run As"
  The FTP Connection manager does contain login/password or are you trying to connect anonymous

  If you take your command line script you noted earlier worked, can that execute via an agent job step of type 'operating sysem command' rather than as an integrations services job

  If you log all you can from the FTP task, there are no other details such as the log entries ftpconnectingtoserver and ftpoperation

  I admit I am a bit puzzled. It smells like the normal permissions complexeties when running under agent but I have not hit it with FTP before. I would think the permissions executing the package would not affect an ftp connection as it should pass the credentials from the FTP connection manager

  Do you have any access to the remote server logs to see what its recieving. For example I pulled the following from my w2k3 ftp server and can see the actual commands\operations.

  20:47:22 172.27.13.104 [14]USER ******* 331 0
  20:47:22 172.27.13.104 [14]PASS - 230 0
  20:47:22 172.27.13.104 [14]CWD /testfiles 250 0
  20:47:22 172.27.13.104 [14]sent /testfiles/amex.20040810 226 0
  20:47:24 172.27.13.104 [14]sent /testfiles/nasdaq.20040811 226 0
  20:47:24 172.27.13.104 [14]sent /testfiles/nyse.20040811 226 0
  20:57:08 172.27.13.104 [15]USER ****** 331 0
  20:57:08 172.27.13.104 [15]PASS - 530 1326

  
  
  

• Sunil Pillappa

  Hi, All,

  I am getting exactly the same problem, FTP task works fine in development, but fails in agent. and yes the server will FTP with no problems and the task works fine if I change

  it to a "delete local file" task instead of a "recieve file" task.

  I have two sql2005 servers and get the same problem on both.

  any help greatly appreciated.

  terry

  
  

• droby10

  So this is occurring all on the same machine Success in designer and failure through agent When something works elsewhere but not under agent its usually a permission issue dealing with what account SSIS is being executed as, which you can control as part of the agent job and the 'proxy accounts' Agent supports. I would not have thought in this case it would matter connecting to a remote FTP server assuming your FTP tasks have appropirate credentials in them...unless there is some issue starting the FTP services on your machine. The FTP task relies on your OS to actually FTP...so fore example if you cannot drop to a command line and FTP to the site then its almost gauranteed that SSIS Will not be able to.

  What happens if you create a small bat file to ftp, assuming it works, then try and execute that via agent

  Also, just to be sure, what happens if you run the package outside agent but NOT in debug. You can do this with either dtexec.exe command line or from within the designer choose "Start without debugging" from the debug menu. (CTRL+F5). This should be a standard step for any pacakge prior to moving to a new environment (test, production) as executing in debug is, well, debug mode and does not reflect a production run as well as outside debug mode. I am old school in some ways and would never move my package to another machine/environment/developer if I have not seen for myself the package execute sucessfully from the command line or using dtexecue.exe which helps build the command line (covered in BOL).

  Hope that helps

  
  
  
• FTP Taks Fail