SSIS - FTP - fails when run from a job, but ok when manually

Question

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.

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.

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().

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.

I have also tried adding a new connection to the connection manager vs. using an existing one - no difference.

I have ruled out any networking/blocking/firewall issues. The server allows the ftp connection.

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.

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.

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 :)

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. :)

Supplemental Data: Server - Windows 2008 Enterprise SP2; Workstation - Windows XP SP3; SQL/SSIS: SQL Server 2008 R2

SQL job step - Type: SSIS Package; Run as: SQL Server Agent Service Account (domain account called sqlagent); Authentication: Windows Authentication

Script Task 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;
}

Henning

Thank you for an excellent first question. It's full of details, examples of what you've tried, the errors encountered and your code. I'd upvote this question everyday and twice on Sunday if I could. – Erda

Have you tried to run isolated code with a simple runas as sqlagent user? – Bellanca

The runas would be something like runas /user:company.com\sqlagent "dtexec /server myserver /SQL folder\MyPackage" – Erda

Sorry - I sound like I dont know what I am doing, but this is from the command line right? I assumed yes... and went with it :) It shows "attempting to start dtexec...", then another command window appears and disappears. Afterwards, in the first command window I have another cmd prompt – Henning

One helpful troubleshooting tip is that once you create a job to run an SSIS package, you can then switch the job to a command-line and it will automatically create the command-line statement to run that package. Then, you can run that command line yourself to iron out any issues. You can also modify the command line to perform extra logging. Try this and let me know what happens. Open a command prompt first, then paste in the dtexec statement. Also, be aware of 32-bit vs. 64-bit issues, which can really throw a wrench into things if you don't know about them. – Entirety

Specifically, you may have to do extra work to get the dtexec command to run the correct-bit version for the package you're running. A search online for this should help. – Entirety

ok - I did run it by command line - with both using the runas for sqlagent - and without it. The same results as before. However, I did run it as 32-bit - just for kicks - and it worked! I am going to have to look up what the difference is so I can fully understand. I have had things not work at all if 32-bit was required - this is the first time it worked one way and not another. Thank you! :) – Henning

Answer 1

I appreciate everyone who responded! I wanted to mark this as answered in case anyone else has the issue too. Making the step within the sql job run as 32-bit mode solved my issue. I am going to research more and find out why and blog about it - hopefully someone else wont have to go through all the moaning and groaning that was going on in my head :)

I am really going to have to start answering some questions here - since you were all so helpful and responsive. Thank you again! :)

Henning

Answer 2

I had this exact problem yesterday. Script failed when run as a scheduled task but it ran successfully when I ran it and the script is using secure ftp. The problem for me was that when I was logged in using my local login I had manually accepted the secure ftp encryption certificate. So when I ran it from my login it ran successfully.

I fixed it by logging in as the user that owns/runs the scheduled tasks and then I invoked the ftp pgrogram(core ftp) and accepted the secured ftp certificate.

I hope that will fix it for you too!

Chromatology

I appreciate this answer - I have something where I use WinSCP for it (another completely separate process) and I had to do what you mentioned for that. However, I am trying this from SSIS script task, which isnt using a separate frp program that I know of. Luckily I dont have to worry about secure ftp on this. :) Thank you though! – Henning

Answer 3

Out of interest have you tried using an FTP Task?

I had a few issues with getting FTP working and I found using one of those tasks helped me.

If you have tried using the FTP Task, did you get any error messages?

Wellmeaning

I didnt try that because I have multiple files to send - I didnt want to make a new connection for each file. For instance, I have 60+ files to send at the moment. I will try that though and post the results. – Henning

Answer 4

Open "integration service catalogs" > Then Right click on "SSISDB" > Choose "Active OPerations" > Stop All job > Now you can run your Ftp job and file will transfer

Wendiewendin