Prev: A public class is not visible in the namespace ... why
Next: Reporting issues and VS 2008 Standard capabilities?
From: mikep on 6 May 2010 19:01
Hi,

I've posted this in the SQL Server programming group as well but thought I'd
try here as its C# involved.

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.bat /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 (this is my assumption)

If I remove the wait for the window to shut, the SFTP does not occur i.e.
The parent window seems to shut immediately 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

Mike
From: Andy O'Neill on 7 May 2010 15:04

"mikep" <mikep(a)discussions.microsoft.com> wrote in message
news:36BEFBD4-D587-40B1-BF82-71A096BBEF24(a)microsoft.com...
> Hi,
>
> I've posted this in the SQL Server programming group as well but thought
> I'd
> try here as its C# involved.
>
> 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.bat /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 (this is my assumption)
>
> If I remove the wait for the window to shut, the SFTP does not occur i.e.
> The parent window seems to shut immediately 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
>
> Mike
First off. I don't think running a console app from a job is a good idea
mate.
Having said that, there's a classic gotcha in running packages etc from a
console directly and they work but they fail in a job. That's because the
job uses different security credentials.
I recommend you take a look at this article though:
http://www.codeproject.com/KB/database/SSIS_SFTP.aspx

 | 
Pages: 1
Prev: A public class is not visible in the namespace ... why
Next: Reporting issues and VS 2008 Standard capabilities?