Scheduled Job Hanging when I'm not Remote Desktopped onto the Server

Question

Scheduled Job Hanging when I'm not Remote Desktopped onto the Server

SQLSimon

Mr or Mrs. 500

Points: 531
More actions
January 7, 2010 at 7:02 am

#141411

Hi,
I have an SSIS package that amongst other things, imports files from a client's FTP server using psftp.exe (secure FTP).
I have created a single-stepped SQL job to run the package and the job step is "run as" a proxy account using a domain account credential.
When I'm remote desktopped onto the server using that domain account, the job runs correctly, whether I manually start it or it is run under a schedule. But when I'm not RDP'd on using that account, the psftp process will hang until its timeout period has elapsed. It will still hang if I'm RDP'd on using a different account.
It will also hang if I run the step under the SQL Agent Service Account.
Any ideas why I should need to be RDP'd on using that account and more importantly, how I can schedule it so that I don't need to be?
Thanks for your help.
The server is SQL Server 2005 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

First guess: What is the protection level of your package? I have had some issue that if I did not use "EncryptSensitiveWithPassword" when I stored the SSIS package on the server it would not run correctly.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

SQLSimon Mr or Mrs. 500 Points: 531 More actions · Answer 2

Shawn,

The protection is "EncryptSensitiveWithPassword".

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

My best guess is the problem lies with the PSFTP executable. I have not worked with this program much so I am not completely familar with it.

To verify you are using the PSFTP executable, calling it from the SSIS package to pull down the files?

How do you call this program?

Can you just use the FTP Task available in BIDS?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

SQLSimon Mr or Mrs. 500 Points: 531 More actions · Answer 4

I have an "Execute Process task", where I specify the path & executable (psftp.exe) and in the "arguments" I specify the IP address of the client server, the user, password and the script file for the ftp commands.

I can't use the ftp task as it needs to be secure ftp and SSIS doesn't (as far as I know) include such a task.

It works from within BIDS & also as a SQL job, providing I'm RDP'd on as the same domain user as the account used to run the job step.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

SQLSimon (1/7/2010)
It works from within BIDS & also as a SQL job, providing I'm RDP'd on as the same domain user as the account used to run the job step.

Well found this entry on Andy Leonard's blog. In the comments of that blog it points right back to a SCC post:w00t:.

In a nutshell, sad to say, it appears that PSFTP does not function correctly unless used interactively. Explaining why it works when you are logged on.

EDIT: Although the last comment on Andy's blog, a user mentions a work around that he did. You might try it to see if it works for you.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

SQLSimon Mr or Mrs. 500 Points: 531 More actions · Answer 6

Thanks Shawn, I'll have a look at his blog.

Thanks for your time.

SQLSimon Mr or Mrs. 500 Points: 531 More actions · Answer 7

I actually looked at this SSC article when I first had the problem. It's not quite the same problem that I have as I can schedule the job to run automatically. I'm not running the job myself, I just need to be RDP'd on when it runs!

I'll carry on digging.