May 31, 2013 at 12:51 pm
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 http://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 http://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 = http://ftp.Connect();
if (ftpConnected) {
errMsg += " - I connected: True; " + cm.ConnectionString.ToString() + "; ";
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;
}
June 3, 2013 at 8:30 am
Just wanted to answer this in case someone else has a similar issue. I made the sql job step runi n 32-bit mode and it worked. I am going to have to research the "why" behind it and blog about it. Hopefully this saves someone else from having a long, frustrating day too 🙂
June 4, 2013 at 4:47 am
Yes that Run in 32bit mode checkbox can be very annoying!
March 24, 2014 at 2:54 am
Hi all,
I've just hit this exact same issue... an SSIS package called from an Agent Job where the FtpClientConnection.Connect method fails for no reason. It works when Run64BitRuntime is set to False, but not when set to True.
Does anyone know why this happens? Can SQL Agent work with FTP in 64bit mode?
Thanks,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply