none
SSIS - ExecuteProcesTask Batch File calls PSFTP.exe - runs fine manually but hangs when run on schedule

    Question

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

    cd C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp
    del Daily.csv
    psftp <email address> -pw <password> -b Shoppertrak.bat
    ren Daily*.csv Daily.csv
    exit

    Here's the text of the second script (shoppertrak.bat):

    mget *.csv
    lcd C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp\archive
    mget *.csv
    del *.csv
    bye

    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.

    Thanks,

    -Dan


    Monday, June 04, 2012 10:52 PM

Answers

  • 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!

    Wednesday, June 13, 2012 7:51 PM

All replies

  • Dan--

    Did the event viewer leave you any clues?

    Monday, June 04, 2012 10:55 PM
  • That's a negative. All the event viewer says is "Package Executed" and then "Package Failed" from the timeout.
    Monday, June 04, 2012 11:51 PM
  • What 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

    Abhinav

    Tuesday, June 05, 2012 1:02 AM
    Moderator
  • RockStar,

    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.


    • Edited by plditallo Tuesday, June 05, 2012 1:44 PM
    Tuesday, June 05, 2012 1:42 PM
  • Dan:

    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

    Tuesday, June 05, 2012 1:57 PM
    Moderator
  • 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 ;)


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Tuesday, June 05, 2012 10:18 PM
  • 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).


    Tuesday, June 05, 2012 11:42 PM
  • 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.


    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, June 06, 2012 12:25 AM
    Moderator
  • 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).

    Absolutely maddening.

    Wednesday, June 06, 2012 12:56 AM
  • 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!

    Wednesday, June 06, 2012 1:01 AM
  • Thanks everyone for the suggestions, please keep them coming!
    Wednesday, June 06, 2012 1:03 AM
  • 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

    Wednesday, June 06, 2012 1:03 AM
    Moderator
  • Danel,

    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 *.csv 
    lcd C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp\archive
    mget *.csv 

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


    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, June 06, 2012 1:16 AM
    Moderator
  • 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.

    :-)


    Wednesday, June 06, 2012 4:03 PM
  • 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.

    something like:

    cd C:\Users\administrator.FLINC\Desktop\Shoppertrak\psftp
    del Daily.csv

    "echo Y | "psftp.exe" <email address> -pw <password> -b Shoppertrak.bat
    ren Daily*.csv Daily.csv
    exit


    • Edited by plditallo Wednesday, June 06, 2012 8:48 PM
    Wednesday, June 06, 2012 5:31 PM
  • Here's what a failed job message looks like in the job history window:

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

    Message
    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.

    Wednesday, June 06, 2012 9:19 PM
  • I 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.
    Wednesday, June 06, 2012 9:21 PM
  • 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!!:-}






    • Edited by plditallo Wednesday, June 06, 2012 11:42 PM
    Wednesday, June 06, 2012 11:14 PM
  • 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!

    Wednesday, June 13, 2012 7:51 PM
  • I realize this is a year old forum thread, but i had the exact same problem and was able to figure it out.  If you call your EXE via a batch file in the Process Task object,   in the batch file put the following on the line BEFORE you call your EXE.   Like this.....

    SET SEE_MASK_NOZONECHECKS=1
    C:\your path and exe goes here.exe

    Wednesday, June 19, 2013 6:52 PM
  • hi

    a "me too" from me

    i tried making the service account local admin, no luck

    a weird thing is it worked for weeks, then started hanging

    Wednesday, September 18, 2013 3:10 PM
  • I know this is an old threat but I've just run into the exact issue.  The weird thing is we have 2 jobs running 2 different packages - the first doing SFTP uploads and the second doing downloads.  They've been running for a year or so and now suddenly stopped working - timing out.

    The download one fails every time and the upload works sporadically - could be when someone's logged into the server.

    Did anyone actually solve with PSFTP or am I going to have to change to WinSCP?

    Thanks,

    Ed


    Ed Clarke | www.atheonanalytics.com

    Thursday, March 06, 2014 3:26 PM
  • Time outs needs to be weeded out by your infrastructure staff.

    Arthur My Blog

    Thursday, March 06, 2014 3:28 PM
    Moderator
  • Sorry Arthur, I'm not entirely sure what you're saying - I should pass the issue to someone else?

    Ed Clarke | www.atheonanalytics.com

    Thursday, March 06, 2014 3:32 PM
  • Yes, SSIS relies on the transport layer for networking activities. It is as fragile or solid as the infrastructure.

    Arthur My Blog


    Thursday, March 06, 2014 3:37 PM
    Moderator
  • Arthur, we're a very small business and we have no 'infrastructure staff' so it's my issue to solve.  We have SQL Server 2008 R2 running on MS Server 2008 on some virtual servers at a cloud hosting company and no infrastructure changes have been made so not sure why the jobs have stopped working.

    The packages execute fine through BIDS (on the server) and through SSMS connected to integration services but as soon as it's executed through a job, it's not playing ball.


    Ed Clarke | www.atheonanalytics.com

    Thursday, March 06, 2014 3:42 PM
  • Ed, your last statement about the job changes the picture somewhat, if you can reliable repro this issue then look deeper into the SQL Server, I suspect there are might be other jobs running that use FTP or heavy networking activity is happening at the time your package runs.

    Arthur My Blog

    Thursday, March 06, 2014 3:47 PM
    Moderator
  • Thanks, I'll take a look but I'm sure that these 2 jobs are the only ones that use FTP and as they fail when they run on the 35 min schedule or whenever I manually execute them I'm not sure there's anything conflicting with them.

    I'm starting to wonder whether anything might have changed on the server that's running the SFTP software 


    Ed Clarke | www.atheonanalytics.com

    Thursday, March 06, 2014 3:59 PM