1

I am trying to sftp(SSH) transfer files from a remote server to a local directory in SSIS. I was recommended to use PSFTP.exe to conduct the transfer via a batch file. In debug mode, this works flawlessly, but when deployed to SSIS Catalog, the job stalls at the point to connect and transfer the files. I have no errors from SSIS. The job just freezes. The user set in the job is in the administrator's group. I am missing something. How do I troubleshoot?

enter image description here

Thank you.

arcee123
  • 101
  • 9
  • 41
  • 118
  • "Stalls" = psftp is in interactive mode and waiting for user input. You need to work out what it's prompting for. Are the SQL Server and the debug host the same or different? This might help: http://stackoverflow.com/questions/16439039/batch-file-for-putty-psftp-file-transfer-automation – Nick.Mc May 12 '17 at 04:26
  • hi Nick. Thanks. Why would it NOT ask for input in debug, but ask for input in Production? and lastly, how would I find out what it is asking? – arcee123 May 12 '17 at 04:28
  • It depends on the differences between your debug and prod environment. Are they the same server? the same windows user? That's why I asked if they are the same server – Nick.Mc May 12 '17 at 04:54
  • For example it might be that you are running this on a different server and no D drive exists. Perhaps you should try the `-batch` switch and see if it fails. You should also be able to find a logging switch that will log why it fails. https://the.earth.li/~sgtatham/putty/0.69/htmldoc/Chapter6.html#psftp-option-b – Nick.Mc May 12 '17 at 05:00
  • please put -batch and -hostkey in an answer form, and I'll give you the points. It works now. – arcee123 May 12 '17 at 19:43
  • I reckon it would be best if you put your sample command line and accepted it as answer. – Nick.Mc May 13 '17 at 02:18

1 Answers1

1

Thanks to @Nick.McDermaid, I found out that adding the flag -batch and the key -hostkey aa:bb:cc:........ fixed my problem. for some reason, even though i have the RSA key approved via CMD line, the ssis package did not see the approval, forcing the acceptance. adding the hostkey flag solved the key issue, and the batch flag turned off the interactive setting.

Community
  • 1
  • 1
arcee123
  • 101
  • 9
  • 41
  • 118