title: SSIS - FTP - fails when run from a job, but ok when manually
body: <p>I am trying to stay away from another batch file, is possible.  I have an SSIS package that pulls data from SQL, creates a text file and then sends those file(s) to an external ftp site.  The package works if I run it manually either from my workstation, or from the server.  However, when I run it as a step in a SQL job, the step fails - the package fails to connect.</p>
  
  <p>The data is pulled using a SQL task, a For Each Loop Container is used to create the text files, then a Script Task is used for the ftp portion.</p>
  
  <p>For troubleshooting purposes, within the script task I put text in after each action wtihin the code, with information to help show where the error is happening.  I acquire the user running the job, the value of certain variables, and the connection string, then write that into the log table.  That is how I know the package fails when it tries to connect - I receive a false boolean value from ftp.Connect().</p>
  
  <p>The package has variables for the ftp connection information and I use configuration files for those values.  However, I have hardcoded the values into the code for testing and it produces the same results.</p>
  
  <p>I have also tried adding a new connection to the connection manager vs. using an existing one - no difference.</p>
  
  <p>I have ruled out any networking/blocking/firewall issues.  The server allows the ftp connection.</p>
  
  <p>I have also tried running the package manually as myself, and as the domain user that runs the SSIS package from within the job (sqlagent domain user).  From both my workstation and the server, running it manually from either account works.  To run it manually from my workstation, I use Visual Studio debugging.  To run it manually from the server, I use "Run Package" while connected to the Integration Services instance on the server.</p>
  
  <p>In the code snippet below, I copied and pasted parts of the script task - it should include all the ftp code.  I didnt paste the part of getting the file name and sending the files.  The error always happens at the connect portion.</p>
  
  <p>What I would like is either 1) help in making it work, OR 2) help in determining how to get more of a response from the ftp.Connect - the boolean only isnt telling me much, OR 3) someone who is an expert telling me I just should use a batch file and stop beating my head against the wall :)</p>
  
  <p>I promise I have done a lot of research, and I have found many posts about how to do the ftp - but not that solve this issue.  Again, the code works, so I know how to do the ftp portion itself.  If there is a duplicate post that I didnt see, I apologize and will read from there instead of making someone post an answer here. :)</p>
  
  <p>Supplemental Data: Server - Windows 2008 Enterprise SP2; Workstation - Windows XP SP3; SQL/SSIS: SQL Server 2008 R2</p>
  
  <p>SQL job step - Type: SSIS Package; Run as: SQL Server Agent Service Account (domain account called sqlagent); Authentication: Windows Authentication</p>
  
  <p>Script Task Code:</p>
  
  <pre><code>string errMsg = null;
  errMsg = "user: " + System.Security.Principal.WindowsIdentity.GetCurrent().Name + "; ";
  ConnectionManager cm = Dts.Connections["FTP_ABN"];
  cm.ConnectionString = Dts.Variables["FTP_url"].Value.ToString() + ":21." + Dts.Variables["FTP_username"].Value.ToString();
  cm.Properties["ServerName"].SetValue(cm, Dts.Variables["FTP_url"].Value.ToString());
  cm.Properties["ServerPort"].SetValue(cm,"21");
  cm.Properties["ServerUserName"].SetValue(cm, Dts.Variables["FTP_username"].Value.ToString());
  cm.Properties["ServerPassword"].SetValue(cm, Dts.Variables["FTP_password"].Value.ToString());
  FtpClientConnection ftp = new FtpClientConnection(cm.AcquireConnection(null));
  ftpConnected = ftp.Connect();
  if (ftpConnected) {    
  errMsg += " - I connected: True; " + cm.ConnectionString.ToString() + "; ";
  ftp.Close();
  if (errMsg != null) { Dts.Log("YES - I am ok: " + errMsg, 0, new byte[0]); }
  Dts.TaskResult = (int)ScriptResults.Success;
  }
  else {
  errMsg += " - I connected: False; " + cm.ConnectionString.ToString() + "; ";
  Dts.Log("Error: " + errMsg, 0, new byte[0]);
  Dts.TaskResult = (int)ScriptResults.Failure;
  }
  </code></pre>