SSIS - ExecuteProcesTask Batch File calls PSFTP.exe - runs fine manually but hangs when run on schedule
lundi 4 juin 2012 22:52
Hi There -- Got a weird one here.
I have a very simple SSIS package that uses ExecuteProcessTask to call a batch file, which calls a process (PSFTP.exe), which downloads a file from a secure FTP server, renames it and uploads the data to one of our SQL 2008 DB's (not R2).
When I execute the batch files manually, they run successfully. When I run the package from inside BIDS, it runs successfully.
When I schedule the package to run using SSIS, and I watch the affected folders while logged into the server, the job (and the package) runs successfully.
NOW... if I let the schedule run while I'm NOT logged in and watching the affected folders, the job hangs and then times out (I programmed the job to timeout after 3 mins, the job only takes about 15-20 seconds to run to successful completion). The package gets to the part where it opens PSFTP.exe, but it hangs at that point. I have to open the server's task manager and kill off the psftp.exe process every time the job hangs -- it's as if the second script doesn't recognize or accept the parameters being passed to it from the first.
The server that is running the job is running Windows Server 2008 R2 Standard, x64 bit, SP1. The batch files are calling/using only local folders in the file system. And I can log in to SSMS as either the local SQL sa or the domain administrator, and start the job from SSMS, and the job will run successfully.
here's the text of the first script (shoppertrakmain.bat):
psftp <email address> -pw <password> -b Shoppertrak.bat
ren Daily*.csv Daily.csv
Here's the text of the second script (shoppertrak.bat):
This is probably some sort of permission issue on my side, but I don't know where to look. Any advice out there would be most appreciated.
- Modifié Danimal_RockStar lundi 4 juin 2012 22:55
Toutes les réponses
lundi 4 juin 2012 22:55
Did the event viewer leave you any clues?
lundi 4 juin 2012 23:51That's a negative. All the event viewer says is "Package Executed" and then "Package Failed" from the timeout.
mardi 5 juin 2012 01:02What user credentials your scheduler runs it from most probably when you try it outside you give it the user credentials which have the access to the server but through scheduler it runs via user which doesnt have the access
mardi 5 juin 2012 13:42
What a frustrating problem!:-\ I think AB82 is on the right track. If you haven't already, try setting up a proxy service account in sql agent-that has the general sql agent's rights plus your access rights to the secured server. See if that works. If it does, take away the rights to the secured ftp server and see if it timesout again. BTW, your access to the ftp server may be hiding under a group/role under NT/Windows.
- Modifié plditallo mardi 5 juin 2012 13:44
mardi 5 juin 2012 13:57Modérateur
you use SQL Agent that runs under its own credentials that in turn too restrictive, therefore the remedy would be in setting the SSIS job step to run using a proxy account that is a domain based account having the permissions similar to yours.
And here is how: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Arthur My Blog
mardi 5 juin 2012 22:18
PSFTP most probably requires full Windows user-profile account. When you execute packages under SQL Job Agent, the user-account is lightweight user account , not the full user account. The behavior you describe when you are logged to the server and your scheduled package works is exactly the type of problems you encounter with those lightweight user accounts.
If you can use third-party solutions, I would recommend you check the commercial CozyRoc SFTP Task. It is completely integrated in SSIS and works properly under SQL Job Agent . No "voodoo magic" required ;)
mardi 5 juin 2012 23:42Arthur, thanks for the suggestion... but unfortunately I already have a proxy account running the job step, it's called "SSIS Administrator" and its access is based on the domain administrator's credentials. Prompted by your response though, I went back and rechecked that the credential and proxy were set up correctly, and they are (at least, they seem to be. I made sure to check the boxes for every subsystem that was available in the proxy).
- Modifié Danimal_RockStar mardi 5 juin 2012 23:47
mercredi 6 juin 2012 00:25
Arthur, thanks for the suggestion... but unfortunately I already have a proxy account running the job step, it's called "SSIS Administrator" and its access is based on the domain administrator's credentials. Prompted by your response though, I went back and rechecked that the credential and proxy were set up correctly, and they are (at least, they seem to be. I made sure to check the boxes for every subsystem that was available in the proxy).
Does the user u mentioned have the full access or atleast the read write access to
C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp folder, of not give it and then try
Also just for trial basis put the files on the some other drive lets say D:\, i presume you are using WIndows 7 or higher as OS and think they have done something wherein you cant access the user folders of some other users directly in your case your prooxy account trying to access files of administrator.FLINC user.
mercredi 6 juin 2012 00:56
It's just the weirdest thing!
I can set the schedule on the job, close SSMS and all windows on the server, and it still runs successfully... the only catch is that I have to be logged in to the server or else the job will hang (and time out).
mercredi 6 juin 2012 01:01
Yes, I granted the user full access to the directory and also the executable, which you can see gets launched from the Task Manager when the job fires off. Also, the first thing the batch file does is delete Daily.csv out of the directory, so it definitely has full access to the directory.
I appreciate the insight though. Thank you!
mercredi 6 juin 2012 01:03Thanks everyone for the suggestions, please keep them coming!
mercredi 6 juin 2012 01:03Modérateur
This still tells me you have an account issue. The weird thing here is the job (I suspect) runs not under your account. Can you you use your account as the proxy?
And I suggest using WinSCP, looks like people have fewer issues with it, or better yet CozyRoc's SFTP task.
Arthur My Blog
mercredi 6 juin 2012 01:16
I havent used the PSFTP so would second arthurZ opinion WinSCP is more stable thing to use though what are you trying to do in these lines
Mget 2wice first time i think you are at directory C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp so it will read the files alright, but next line lcd and again mget? i thkink you are trying to write files to archive so isnt it a diff function then mget something like mwrite available :)
mercredi 6 juin 2012 16:03
Abhinav -- It seemed easier at the time to just 'get' the file twice -- once to drop in the directory for transformation and upload, and then again for the archive. We just needed a backup of what was dropped on the SFTP server in case we discovered any issues. but either way, both scripts work fine when launched manually, and using the job when logged in to the server.
Thanks for the WinSCP suggestion, I will give that one a shot and get back to you.
- Modifié Danimal_RockStar mercredi 6 juin 2012 16:04
mercredi 6 juin 2012 17:31
If you aren't already using these task types, try changing your package to have an execute process task execute the batch file(s). You might have to do an echo y in the batch job(s) to force a confirmation response to press on with the username/password.
"echo Y | "psftp.exe" <email address> -pw <password> -b Shoppertrak.bat
ren Daily*.csv Daily.csv
- Modifié plditallo mercredi 6 juin 2012 20:48
mercredi 6 juin 2012 21:19
Here's what a failed job message looks like in the job history window:
Executed as user: FLINC\administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:41:00 AM Error: 2012-06-06 11:42:31.07 Code: 0xC002914C Source: Execute Process Task - Download Shoppertrak Daily File Execute Process Task Description: The process timed out. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:41:00 AM Finished: 11:44:01 AM Elapsed: 180.418 seconds. The package execution failed. The step failed.
...and here's what a successful message looks like in the job history window:
Executed as user: FLINC\administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:59:54 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 1:59:54 PM Finished: 2:00:08 PM Elapsed: 13.9 seconds. The package executed successfully. The step succeeded.
mercredi 6 juin 2012 21:21I suspect that the point of failure is either the PSFTP executable itself, or the fact that a batch file is calling another batch file and Windows Server 2008 R2 doesn't like that for some reason.
mercredi 6 juin 2012 23:14
Ok, here's something I wouldn't normally propose as a solution, but in this case, any progress toward debugging is worth a shot!:-)
Let's take everything *SSIS* out of the mix for a minute... and create a stored proc that will invoke one of the batch jobs (read: use xp_cmdshell). See if you can run the sproc interactively through ssms first. If it works, schedule the stored proc as a sql server agent job. If the trouble reappears, comment out the ftp portion, then add a line or two to output a text file to leave a footprint. Choose a valid path on the box that is running the agent, valid on your local box and valid on the box where you think the job should be reaching.
If you're able to see your output file on any box, then the next issue to examine will be the registry keys that may still contain an old ip address/domain name that the ftp software is using. Look for, or look at the host file on the sql server box running the agent to be sure the DNS name/ip address resolves as expected. Do the same on your local machine/IDE environment. If that doesn't turn up anything, re-examine the whole domain account issue.
If you can get the sproc calling the batch job to work all the way through, disable the batch/process tasks, create an execute sql task in its place that calls the newly created sproc. Try running the package as a scheduled job with these changes. If it works, call it a day!!:-}
- Modifié plditallo mercredi 6 juin 2012 23:42
mercredi 13 juin 2012 19:51
Got it solved, finally.
I had to do the ExecuteProcessTask part of the job in the Windows Task Scheduler, and rig it to run "even when not logged in".
kinda lame, but it works. I'm calling it a day.
Thanks everyone for your replies!
- Marqué comme réponse Danimal_RockStar mercredi 13 juin 2012 19:51