SSIS - Connection Time Out From SQL Job

Advertisement

RogerRabbit9999
Guest

SSIS - Connection Time Out From SQL Job

I know this is question is common, as I think I have read every forum post on the subject. I've been researching this issue for three days now.
I have checked https://winscp.net/eng/docs/faq_scheduler, and every other on-site article I can find. I'm either missing something small that's right in my face, or my problem is unique in some way.

I have a get file script that runs fine from cmd prompt, and scheduled task/BAT file, but will not run when initiated by a SQL Job/SSIS pkg. I set the entire process up on an a domain administrator account. The domain account is the same account as the SQL Agent Account. The account has full read/write access to all the file and directories referenced. I can also manually execute the SSIS pkg from within visual studio and the process completes fine. The only time it fails is when I execute the SQL Job that calls the SSIS pkg. I've tried calling WINSCP.com as well as WINSCP.exe.

At this point, I'm pulling my hair out. Any help will be greatly appreciated.

Log file indicates the process gets stuck at:
---------------
. 2016-10-13 10:09:02.856 Connecting to ftp.sitename.com ...
. 2016-10-13 10:09:17.936 Timeout detected. (control connection)
. 2016-10-13 10:09:17.936 Connection failed.
---------------

This the log from the successful Command Prompt execution:
---------------
. 2016-10-13 09:44:19.001 Connecting to ftp.sitename.com ...
. 2016-10-13 09:44:19.080 Connected with ftp.sitename.com , negotiating TLS connection...
< 2016-10-13 09:44:19.126 220 Welcome to the ftp.sitename.com FTP server.
> 2016-10-13 09:44:19.126 AUTH SSL
< 2016-10-13 09:44:19.158 234 Authentication method accepted
. 2016-10-13 09:44:19.579 Verifying certificate
...
---------------

SSIS Execute Process Task:
---------------
- Executable: \\serverName\c$\Program Files (x86)\WinSCP\WinSCP.exe
- Arguments: /script=GetFiles.txt /log=WINSCP.log
- Working Directory: \\serverName\d$\Data Import
---------------

WINSCP Script:
---------------
option batch abort
option confirm off
open ftps://user:pw@ftp.sitename.com -passive -explicitssl -hostkey="ssh-ra 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
cd /Directory1
get -latest *1.txt Directory1\File1.txt
get -latest *2.txt Directory1\Directory2\File2.txt
exit
---------------


BAT File:
---------------
cd c:\Program Files (x86)\WinSCP
winscp.com /script="d:\Data Import\GetFiles.txt"
---------------

Reply with quote

Advertisement

RogerRabbit
Joined:
Posts:
1

Thanks for the reply Martin. Can you help reason this out?

I have access from that same account when I run the process from cmd prompt. I also have access from that same user when the scheduled task runs.

Right now I have a scheduled task that runs a BAT file that runs the script. This is a placeholder process because I can't get the project to run via the SQL Job/SSIS.

What would prevent network access from the SQL job/SSIS pkg? I'm using the same user for everything, which is also the user setup as the SQL Agent account.

Reply with quote

Advertisement

You can post new topics in this forum