troubleshooting Question

SSIS 2008 R2, Execute in SQL Agent not working for WinSCP.exe call

Avatar of Tony303
Tony303Flag for New Zealand asked on
File Sharing SoftwareMicrosoft SQL Server 2008SSIS
4 Comments2 Solutions1496 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Rimvis

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros