Schedule SSIS package containing Execute Process Task for PSFTP
-
I need help. I know that there have already been several forum posts about scheduling SSIS packages using SQL Server Agent but I haven't found a solution that complete addresses my issue.
I have a SSIS package that has a Work Flow task that runs a query to extract data from an SQL database and then saves the file in a directory on the database server. Then an Execute Process Task calls a batch file that contains commands to run PSFTP and move the local file to the FTP server. Once the file is moved to the FTP server, the batch file moves the local file to an archive directory.
This SSIS package runs fine in Visual Studio but then I try to schedule this package using SQL Server Agent the package hangs on the Execute Process Task. I am using a SQL Agent proxy account to run the package and have also logged onto the database server using the SQL Agent proxy account to establish the PSFTP fingerprint for the account.
My Execute Process Task has the Executable value of:
"D:\SSIS\launchftp.bat"
My launchftp.bat file looks contains the following command:
d:\AHA_SSIS\Putty\psftp.exe 65.218.158.149 -l ftpuser -pw password -be -b d:\SSIS\pbd.txt
The pbd.txt file contains the following commands:
cd members
lcd d:\Export_Files\PBD
put members.txt
exit
Everytime I try to schedule the package, it appears that my Execute Process succeeds but the members.txt file is never placed on the FTP server. If I run the launchFTP.bat file outside of the SSIS package it works fine.
Any suggestions? Does my batch and accopanying text file make sense.
-
Did you ever find a solution to this problem? I have the exact same scenario: Execute Process Task that runs PSFTP.EXE, works in Visual Studio but hangs once scheduled as a SQL Server Agent job on the server. Thanks.
-
No, unfortunately I never found a solution. I've tried most of the solutions that have been mentioned but none were successful.
-
If you can use third-party solutions, check the commercial Cozyroc SFTP Task. It is fully integrated in SSIS and you will not experience issues like the above.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
The CozyRoc solution is out of our budget and we have been using PSFTP for years with SQL 2000. There must be a way to get this working in SSIS. I know that PSFTP wants to store an encryption key the first time you run it per machine per user per FTP connection host. The prompt for the "y/n" on whether to store the key can cause the script to hang. Once you save the key, it does not prompt you again. We went through this on SQL 2000 as well. But, I have already run this script as the same user that the SQL Agent is using and already stored the key. No matter what window mode I use, i.e. hidden, normal, etc., it still hangs and I cannot "trace" the process within the command shell. We are able to get the package to run, just not as a scheduled SQL agent job. I think it may have something to do with the prompt. Has anyone cracked this nut yet? Thanks!
-
If it is asking a Y/N question then I have a thought.
Login to the machine as the same user that SQL/Agent login to the machine as, ie: if SQL uses domain1\sqlusername to login to the box, YOU use that login to get in as well, then manually execute that command, then YOU can tell it yes.. You may also see other errors.
Also, I am surprised that a command-line tool doesn't have an option that basically says "Say Yes to every question you'd ask a user."
CEWII
-
Did you ever find a solution to your putty issue? I'm having the same problem...I'm sure it is permission but I don't know where to start looking.
Thanks
Sena
-
Elliott W's recommendation was spot on - we logged on interactively using the account the scheduled task would run as, ran the command and,yes, it was prompting on the initial run - after we answered in the affirmative the Scheduled Task didn't have any problems running. There may be a command line option to avoid all this but I didn't find it. Sorry for not following up sooner...
-Todd
-
Todd, thanks I'll give this a try --- how can you get the job to stop, I've tried but it just will not STOP!
-
Sena Ramsey (5/17/2010)
Todd, thanks I'll give this a try --- how can you get the job to stop, I've tried but it just will not STOP!You may try to use Task Manager to kill the PSFTP.EXE process. I haven't used this particular piece of software, but have had similar issues with other executables that were hung. I think once you kill the EXE process then the agent job will report as faiiled.
-
That worked great --- thanks, I did'nt even think to look at if it was running:hehe:
-
I had this problem. I solved it with steps:
1. Run psftp.exe from command promt, it will ask you to reply "Y" and press enter (!!!)
2. Created SSIS package with Process Task to execute psftp.exe and saved package in file system in same folder with psftp.exe
3. Scheduled in windows job with same profile that I used to run psftp.exe (see step 1).
Job executes .bat file to execute SSIS package as "DTEXEC.EXE /F PackageName.dtsx /Decrypt password"
-
If you need to run only your batch file, you do not need to create SSIS package.
You can just schedule windows job to run your file D:\SSIS\launchftp.bat"
but first you need to run psftp.exe from command prompt.
-
I might -- maybe, possibly, with luck -- have found a solution for your issue. Did I say: no guarantees?
I needed to execute command-line FTP from an SSIS package. Worked fine when I executed the package directly, but failed in Agent. Went back & forth with the DBA team for a couple months. They assigned necessary (and even some unnecessary) permits to the Agent proxy -- didn't help. The @#%^@#!! thing always hung in Agent.
However, since I had a DTS package that did exactly the same thing, I knew (1) FTP was correctly installed and (2) FTP w/in DTS could be executed from Agent. Also, since I could execute the SSIS package, it was clear that it was a permissions issue, either with Agent or its proxy or Agent's interaction with SSIS.
After several weeks of frustration, tried something stupidly simple: created a new folder "C:\Program Files\FTP" and copied http://ftp.exe from its windows\system32 home into the new folder. I then changed the Execute Process task in SSIS to reference http://ftp.exe in the new folder instead of system32 and -- bing-o-vast -- the Agent job suddenly worked like a charm!
The issue evidently has to do with Agent/SSIS requiring some type of elevated permissions to run programs in system32. I don't know more than that and, now that my job works, don't care a whole lot. However, I had gone to a number of online forums and never found any hint that Windows utilities required different permissions than other programs.
Good luck and good afternoon.
-
I have had the same problem described above and I went ahead and ran it manually as the user that the SQL Server Agent runs under and selected 'y' on the key cache question and it is still just clocking. I know that the command line psftp.exe step is the culprit since when I remove that step and leave all of the settings/permission/users unchanged the package works like a champ under the SQL Agent Jobs.
Any other ideas?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply