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"
---------------
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"
---------------