Topic "SSIS - Connection Time Out From SQL Job"

Author Message
RogerRabbit9999

Guest


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"
---------------
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
It looks like your "SQL Agent Account" does not have a network access.
RogerRabbit
[View user's profile]

Joined: 2016-10-13
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
I'm no SSIS expert. I'm just pretty sure, this has nothing to do with WinSCP.
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License