Avatar of Tony303
Tony303
Flag for New Zealand asked on

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
Microsoft SQL Server 2008File Sharing SoftwareSSIS

Avatar of undefined
Last Comment
Tony303

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rimvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tony303

ASKER
Hi Rimvis,

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.
SOLUTION
Tony303

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tony303

ASKER
My actual problem was highlighted by Rimvis's help, My solution cured my issue.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23