From: mikep on 6 May 2010 18:56
At a client I'm working at, we need to send files via SFTP to a server on
completion of several steps of a SQL Job. The client has a command line SFTP
program which requires when invoking, to impersonate a particular user
otherwise the SFTP connection is not accepted.
I have developed a small C# console app which uses
System.Diagnostics.Process.Start to invoke the command line SFTP program as
the required user and pass in the correct arguments (Windows RunAs does not
allow a password to be passed in). The console app also waits for the SFTP
program to complete. I have a DTS package which puts together an appropriate
batch file and is then executed using the console RunAs app at the end of the
DTS . i.e. NewRunAsConsoleApp.exe SFTPCommand /U:User /P:Password etc
If I run the DTS from the designer, everything works fine. I see the batch
file run using the NewRunAs console app, the SFTP command window open, the
SFTP works and the window closes and then the batch file window closes.
If I then add the DTS as a step in the schedule the job, it does not work.
The process hangs waiting for the SFTP command window to shut. i.e. The
window that was invoked using Process.Start, does not seem to be able to get
a handle on the SFTP window that opened and hence the parent window just sits
there waiting for its completion.
If I remove the wait for the window to shut, the SFTP does not occur i.e.
The parent window seems to shut immeadiately without executing the batch file.
I realise that the SQL Job somehow supresses the window being visible as in
when just running the DTS package but does anyone have any ideas on how to
get this to work?
What is the SQL Job scheduler doing that interferes with the Process.Start
and the WaitForExit calls?
Any ideas gratefully received
Thanks in advance