SSTS Blog

Some news and tidbits that we share

SSIS, pscp, RUNAS

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Jul 19 in Blog 0 Comments

Native SSIS does not support secure ftp. Therefore, a typical way around this is to use an execute process task in SSIS that calls pscp (or similar process) to transfer files. In Development you may be running as a different user than the SQL Agent process. Secure FTP/copy when run the first time may result in the need to deposit a digital fingerprint in a file under the user account that is performing the secure copy. When you invoke in BIDS the first time you will see a popup window when this happens asking for permission. However, when you run from an SQL Agent job the question goes unanswered and the job hangs. In order to acquire the digital fingerprint file for the SQL Agent user account invoke pscp and use Windows RUNAS and specify the user that the SQL Agent service runs as.

i.e.

RUNAS /user:DOMAIN\sqlgentaccount "C:\Tools\pscp.exe -l user -pw password "E:\source_file"
"remote_acct@remote_ip:/dir/dest_file""

There may be a way to avoid this using a pscp option. If you know of one please post your idea.

 

Tags: Untagged
Hits: 76172

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments