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.