Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

martin

I'm no SSIS expert. I'm just pretty sure, this has nothing to do with WinSCP.
RogerRabbit

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

Re: SSIS - Connection Time Out From SQL Job

It looks like your "SQL Agent Account" does not have a network access.
RogerRabbit9999

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