SSIS 2008 R2, Execute in SQL Agent not working for WinSCP.exe call
Hello all,
I know there is a myriad of stuff out there on this, but it isn't getting my problem solved.
Here is where I am stuck.
My package has many steps.
The process works in BIDS and when I use DTEXECUI.exe
It does not work in SQL Agent
I am concentrating on the first step which is an FTP pickup and copy to my network.
I am using WinSCP.exe in an Execute Command Task.
The executable is C:\Program Files (x86)\WinSCP\WinSCP.exe
I have WinSCP installed and configured on my ETL server which houses contains the Package.
The Execute Command Task has an argument like this...
<Login and Connect> /console /script="My UNC path to script file"
I have a proxy account running the SQL Job, the proxy account has access to the location of the script file and is a sysadmin of the ETL SQL Server.
I have tried this in 32 bit mode in the SQL Job.
What I can see in Task Manager when the job runs, is DTExec.exe start, and then I can see WinSCP.exe start.
The SQL Job stays executing, no transfer from FTP is happening.
I have to manually stop the SQL Job.
When the job is stopped the WinSCP.exe is still running. I End Process in Task Manager.
Remember, logged into the ETL server as the same domain account as my Proxy I have no issue running in BIDS or DTEXECUI.exe either in 32 o 64 bit mode.
It is doing my head in!!!
Help me please.
T
I know there is a myriad of stuff out there on this, but it isn't getting my problem solved.
Here is where I am stuck.
My package has many steps.
The process works in BIDS and when I use DTEXECUI.exe
It does not work in SQL Agent
I am concentrating on the first step which is an FTP pickup and copy to my network.
I am using WinSCP.exe in an Execute Command Task.
The executable is C:\Program Files (x86)\WinSCP\WinSCP.exe
I have WinSCP installed and configured on my ETL server which houses contains the Package.
The Execute Command Task has an argument like this...
<Login and Connect> /console /script="My UNC path to script file"
I have a proxy account running the SQL Job, the proxy account has access to the location of the script file and is a sysadmin of the ETL SQL Server.
I have tried this in 32 bit mode in the SQL Job.
What I can see in Task Manager when the job runs, is DTExec.exe start, and then I can see WinSCP.exe start.
The SQL Job stays executing, no transfer from FTP is happening.
I have to manually stop the SQL Job.
When the job is stopped the WinSCP.exe is still running. I End Process in Task Manager.
Remember, logged into the ETL server as the same domain account as my Proxy I have no issue running in BIDS or DTEXECUI.exe either in 32 o 64 bit mode.
It is doing my head in!!!
Help me please.
T
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My actual problem was highlighted by Rimvis's help, My solution cured my issue.
ASKER
That has worked a bit for me now.
I have managed to produce a log file per execution.
My Dev box in BIDS gave a file which didn't error and did the transfer as expected.
I then moved the package to my ETL server and ran it, the log file gave me an interesting result. Win SCP is asking to overwrite a file!!
The trouble is where I expect the file there is no file.
I then searched the ETL server for the filename I know is the file in question.
It is in C:\Windows\SysWOW64!!!
I assume this is some default WinSCP folder location and my setup on the ETL server is in question.
I shall look again when I am in the office tomorrow, currently dialled in from home.