My agency recently upgraded to SQL Server 2008 R2. As part of the upgrade, I converted an SSIS package which among other tasks, needs to be able to FTP and SFTP files. The software I am using for the FTPing is SecureFX. The problem is that the SSIS package works when run manually, but not when scheduled. By 'doesn't work' what I mean is that all the steps appear to run, the but tasks that do the FTPing fail to run. I know this a typical problem that seems like a permissions issue, but hear me out on what I have discovered when trying to problem solve this: I logged onto the server remotely and looked in Configuration Manager where I can see the account that Agent runs under. So, then I logged onto the server *as* the Agent account. When I run the package manually while logged onto the server as the agent account, the package works perfectly. In other words, the FTPing happens as expected. If I understand correctly, Agent runs the package with the same account when the package is scheduled. So, can it really be a permissions problem? Something else to note: I have the SSIS package logged with the 'preexecute' and 'on error' events checked. When I run the package scheduled in agent, it appears to generate the same rows as when I run it manually - with no 'on error' events firing. So, I do not get an error and all tasks seem to run, but when the package is scheduled, the FTP itself fails to happen. SecureFX has a log feature itself. When the SSIS package is run manually, the SecureFX log file is updated. When the SSIS package is run scheduled, the SecureFX log file is unchanged - like the program was never called to do anything. But I can see in the SSIS log table that the step to FTP was 'preexecuted' and then the following task appears in the next line of the table. In case this helps: What exactly is the task doing when I want to FTP or SFTP? The SSIS task is an "Execute Process Task". This task is calling a batch file on our LAN. It is the batch file which is calling SecureFX to do the actual FTP or SFTP. I've been struggling with this for weeks now. Any help would be GREATLY appreciated. JJ

Hello SusanSmith_, In short, I believe it is a security issue. Perhaps the step in the agent job that executes the package is set to run using a proxy (is it possible that you overlooked this item)? I trust SecureFX gets executed by a command line via a batch file, so it simply does not log anything, not the SecureFX itself.Arthur My Blog

I agree with Arthur that it's going to turn out to be a permissions issue. Can you add "Echo some text >> logfile.txt" statements to the batch file? My guess is the batch file can't find resources it needs, and fails "silently" by shouting it's error messages into the void. Talk to me now on

Arthur and Todd: Thanks for your replies. re: "I trust SecureFX gets executed by a command line via a batch file..." Yes! re: proxy: I believe that a proxy is something you have to add on purpose? I didn't add any proxies, unless one got added without my knowledge? For Echo text: I will definitely give this a try. I'm not sure exactly what 'echo' is about or what you mean by 'some text', but I will try to research this and give it a try and let you know what happens. Thanks! - JJ

Todd: Here's what I did: 1) I confirmed that the job completes correctly when logged in under the Agent account and when run manually. 2) I added the following text to my batch files: In the file that does FTPing: echo "About To Transfer tblFacility File" >> BatchLog_GetStateData.txt echo "About To Transfer tblHistFacName File" >> BatchLog_GetStateData.txt echo "About To Transfer tblAbsFacil_IDmatches_LCOG File" >> BatchLog_GetStateData.txt echo "The End of Batch File" >> BatchLog_GetStateData.txt And I confirmed by running that batch file manually that the file BatchLog_GetStateData.txt gets created and is filled correctly. In the file that SFTPing, the following text was added before and after the command line to do the SFTP: echo "About To SFTP 4 files to State" >> BatchLog_SendDataToState.txt echo "END of SFTP files to State" >> BatchLog_SendDataToState.txt 3) I re-ran the SSIS package manually just to make sure that the changes above would work as expected. Problems: a) the first .bat file ran fine in terms of doing the FTP, but it failed to create the file: BatchLog_GetStateData.txt. b) the Process Execute Task actually failed on second .bat file. I got that red box and the following error text: Error: 0xC0029151 at Secure FTP XML Files To State, Execute Process Task: In Executing "\\clsrv111\programr\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer\TransDataToState_CopyFilesCallFTP.bat" "" at "", The process exit code was "1" while the expected was "0". Task failed: Secure FTP XML Files To State Warning: 0x80019002 at pkgCTS_AbsFacil_StateExchange: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "pkgCTS_AbsFacil_StateExchange.dtsx" finished: Failure. When I REM (comment out) the Echo text, and re-run the SSIS package, it once again runs fine when run manually. Bottom line: I don't think SSIS likes those Echo lines and didn't see the point in trying to run the package scheduled with the Echo text in there when it couldn't run manually. Does anyone have other ideas I can try??? I can see that it might be a permissions issue, but I can't understand how the job will run fine when I run it manually on the server itself and when logged in as the Agent account, but it fails to run the SecureFX commands when the package is run scheduled. - JJ

The fact that it runs fine manually, and the Echo generated an error by being unable to write to the file indicates that there definitely is a pathing and permissions issue. When you run the package manually, DTExec "starts" with your current path as the "working directory". The batch file writes your BatchLog file there, doesn't it? When SQL Agent starts running the package... what's the "working directory"? Someplace else. A spot where you (or the account running the package) don't have rights to write files to. A place where your SFTP process probably doesn't either. If that's the case, it should be solveable by explicitly specifying a working directory in your batch file or Execute Process Task. A simple "CD" command ought to do it. Talk to me now on

Todd: I so appreciate your ideas. Here's what I did to test your theory: 1) I added text to the Working Directory property of both of the Execute Process Tasks which do the FTPing. I added the directory that the batch file resides in. The batch file resides on a shared directory on our LAN. 2) I ran the job manually to make sure that that still works. It did. 3) I imported the job into the MSDB database, which is what the scheduled job runs. 4) I ran the scheduled job to see what happens. Neither FTP process ran. Bummer. You may be interested to know what does run: One of the batch files has two parts to it: a) copy files from a shared directory onto the c:\ drive of the database server. b) the batch file (which still resides in that shared directory) then calls the SecureFX command which SFTPs the data from the c:\ drive of the database server to the final destination at a State government database server. What is interesting is that the copy of the files from the shared directory to the c:\ drive DOES work when the job/package is run scheduled. Also, as an FYI: the other Execute Process Task, which does the regular FTPing is much simpler. It moves data from our own FTP server to our own shared directory. The batch file and the shared directory for the file destination are the same location. And that is the same location that I changed the Working Directory to. I don't know if this is relevant or not, but I wonder if you picked up on the part where I mentioned that I can run the job manually under the same account that runs the job scheduled - and when run manually, it works. Any other ideas?!? Or do you think I misunderstood what you meant by changing the Working Directory? Thanks!- JJ

Oh! I just had another thought: Since the batch file code which copies files from one drive to another works, but the SecureFX command lines do not, what if I changed the Working Directory to be the location where SecureFX executable is installed? Is that dumb or worth a try? - JJ

You can try that, but I don't think you'll get anywhere. My thinking on the working directory is that you don't truly know where your batch file is going to get "started in". When you start it from the command line, the batch file's working directory is the directory where you executed it from. Almost surely exactly what you expect it to be. But when you execute the batch file from SSIS, that's not going to be the case. SSIS is going to have picked a working directory - possibly C:\User\<username>... which may not exist, or have restrictions, ... Log in to the machine as the proxy user. Go to Start->Run and type in the command line for the batch file. What happens? Talk to me now on

I logged onto the server as the account that runs Agent. Then, I did Start, Run. Then I entered the path and name of one of the batch files which does an FTP. The file ran just fine - ie, the FTP happened as expected. (I verified that the FTP happens correctly by changing one of the the files on our LAN so that I can tell whether the FTP over-writes the file or not.) Does this experiment help us figure out what is going on? Thanks!- JJ

The experiment says the working directory theory I was operating under is probably not correct. What I would try to do is capture the output of the SecureFX call in your batch file. It seems that SSIS believes it's calling your batch file correctly, and the batch file is not returning an error condition. It also seems like SecureFX doesn't think it's getting called... or doesn't write anything to its log if/when it is called. The only explanation I can think of is that the batch file is doing something different (or operating differently) in manual vs. job mode. Try to add a ">> BatchLog_GetStateData.txt" to the end of the call in your batch file to SecureFX, something like this: SecureFX thisserver username pwd put thisfile.txt >> BatchLog_GetStateData.txt Talk to me now on

Todd: Thanks for not giving up on me. I came into the office on my vacation day today just to address this issue. That's how much I need the help and appreciate it. It took me a while to write this reply because I initially didn't understand your reply and spent several hours trying to research the 'put' command. But don't worry, I finally figured out what you were really saying. Here's what I did: I focused on a single line in one of the batch files. This is what the line looks like before I made any changes: REM Transfer tblFacility.txt "C:\Program Files\VanDyke Software\SecureFX\sfxcl.exe" /s:i /LCOG tblFacility.txt P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer This is the line after I made the change: REM Transfer tblFacility.txt "C:\Program Files\VanDyke Software\SecureFX\sfxcl.exe" /s:i /LCOG tblFacility.txt P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer >> BatchLog_GetStateData.txt So, you can see that I added your trick of ">> BatchLog_GetStateData.txt" - which is so cool! RESULTS: The above line runs just fine when I run the batch file only by itself--not as part of the SSIS package. When the batch file is run by itself, the file BatchLog_GetStateData.txt gets generated correctly and the FTP occurs as desired. However, when I run the the entire SSIS package, ***even manually***, the .txt file does not get created and the FTP does not happen. Summary: I'm logged in under the same account on the server for both tests. If I run the batch file by itself, it works with the change in command line to write to a .txt file. If I run the entire SSIS package under the same SQL Agent account, the batch file fails to work when ">> BatchLog_GetStateData.txt" is added to the SecureFX command line. Just to be thorough, I converted the command line back to the original state without the added text at the end. Then I ran the SSIS package manually again under the same SQL Agent account. This time, the batch file DID the FTP as desired. It just fails when a) I try to record the information to a .txt file using the >> trick or b) when I schedule the SSIS job. I started thinking again about permissions and writing files to the LAN. I thought I should point out the following: The SSIS package successfully creates .xml files and puts them in the same folder as the batch file/log files. The SSIS package successfully creates these .xml files whether I run the SSIS package manually or scheduled. The package is creating the .xml files by running an Execute Process task and using the bcp.exe utility. My thinking is, what an odd permissions problem if the package CAN write files to the same folder using bcp.exe. Is it then some kind of permissions problem related to batch files but not bcp.exe? Could this be an SSIS bug? Any other tests I could try? Thanks! - JJ - JJ

Darn it - sorry for getting your name wrong initially!!!. I fixed it now.- JJ

I highly doubt this is an SSIS bug of any kind. It's an interaction issue between SSIS and the OS, and we just need to figure out the "right" way of asking it to do what we want. Can you try chaning your config of the Execute Process Task such that the "application" is NOT the batch file, but is CMD.EXE instead? So the application is CMD.EXE, and the arguments are "/C <your command line here>". That starts a new command shell that then tries to execute your command line. Talk to me now on

I did just as you suggested. I put CMD.EXE in the "Executable" row and I put "C/ \\path\...\File.bat" in the "Arguments" row. I verified that the modified package runs fine manually. Both FTP processes work fine even with the CMD.exe change. Then I imported the modified package into MSDB which is what I do for production packages and ran the package scheduled. It didn't work. Per usual, the package ran without producing any errors, but both FTP tasks failed to actually run. Thoughts?- JJ

Hello SusanSmith_, As part of the troubleshooting, could you please try running just this .bat file from a SQL Server Agent job (no package involved at all)? I am hoping to isolate the issue to just this bat file. If the file runs, will look into what to modify in the execute process step of the package, otherwise I will suggest changing the way you execute the SFTP process. May be WinSCP is the next approach to try.Arthur My Blog

ArthurZ: Thank you for continuing to follow my problem. Your idea was excellent. I've spent all morning doing test after test after test doing variations on your idea and following where the tests lead. I could give you all the details, but I think you are probably only interested in the bottom line, which seems like a real "Doh!" moment. (I'm slapping my head.) Solution to all my heart-aches: Change all references in both batch files from the "p:\" drive to using UNC. When I did that, everything magically started working! (At least all went well now. I'm not really going to count it as good until tonight's normally scheduled run, but hey, what could go wrong? :0) ) To both Todd and Arthur: Thank you, thank you, literal-tears-in-my-eyes THANK YOU!!! I was well and goodly stuck. This seems like something that I should have figured out on my own, but I just couldn't get it. I greatly appreciate your patience and assistance. Aside from helping with this one problem, you both gave me some new tools for problem solving in the future too. Thanks!- JJ

Thank you for your warm words SusanSmith_, and glad you have a happy ending! If you ever experience another issue do not hesitate to drop by our MSDN Forum.Arthur My Blog