I figured out a way around this while still keeping the pass phrase for added security. Since the problem was with SQL Agent not recognizing that Pageant was loaded, I created a bat file using DTExec.exe to call the SSIS package. The bat file looks something like this:

DTExec /SQL "\sFtpPackage" /SERVER WHTRANSFER /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

I then created a Windows Scheduled Task to call the bat file. By executing this way, Pageant is seen and the script is able to get the pass phrase from it and execute successfully.

Thank you for your guidance and great product! I am able to mark this project finished now.

Cheers!
Sumac

I guess it does not see pageant as it running in different session. You can store the private key unencrypted and load it explicitly in stored session instead.

Ok, that helps in the troubleshooting. The regular log tells me what is going on. It isn't picking up the passphrase for the private key from Pageant. It's like it isn't seeing that Pageant is running. Is there a way to force it to look for Pageant key or something additional I can add to the script to point it in the right direction?

Much thanks!

Here is the log file on SQL Agent run:(I changed connection specific information for security reasons)
. 2009-11-05 09:41:07.393 --------------------------------------------------------------------------
. 2009-11-05 09:41:07.393 WinSCP Version 4.2.3 (Build 494) (OS 5.2.3790 Service Pack 2)
. 2009-11-05 09:41:07.393 Login time: Thursday, November 05, 2009 9:41:07 AM
. 2009-11-05 09:41:07.393 --------------------------------------------------------------------------
. 2009-11-05 09:41:07.393 Session name: mylogin@ftp2.ftpsite.com
. 2009-11-05 09:41:07.393 Host name: ftp2.ftpsite.com (Port: 22)
. 2009-11-05 09:41:07.393 User name: mylogin (Password: No, Key file: Yes)
. 2009-11-05 09:41:07.393 Tunnel: No
. 2009-11-05 09:41:07.393 Transfer Protocol: SFTP
. 2009-11-05 09:41:07.393 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2009-11-05 09:41:07.393 Proxy: none
. 2009-11-05 09:41:07.393 SSH protocol version: 2; Compression: No
. 2009-11-05 09:41:07.393 Bypass authentication: No
. 2009-11-05 09:41:07.393 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2009-11-05 09:41:07.393 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2009-11-05 09:41:07.393 SSH Bugs: -,-,-,-,-,-,-,-,-
. 2009-11-05 09:41:07.393 SFTP Bugs: -,-
. 2009-11-05 09:41:07.393 Return code variable: Autodetect; Lookup user groups: Yes
. 2009-11-05 09:41:07.393 Shell: default, EOL: 0
. 2009-11-05 09:41:07.393 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2009-11-05 09:41:07.393 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2009-11-05 09:41:07.393 Local directory: default, Remote directory: home, Update: No, Cache: Yes
. 2009-11-05 09:41:07.393 Cache directory changes: Yes, Permanent: Yes
. 2009-11-05 09:41:07.393 DST mode: 1
. 2009-11-05 09:41:07.393 --------------------------------------------------------------------------
. 2009-11-05 09:41:07.393 Looking up host "ftp2.ftpsite.com"
. 2009-11-05 09:41:07.409 Connecting to <ip address> port 22
. 2009-11-05 09:41:07.581 Server version: SSH-2.0-OpenSSH_5.2p1 FreeBSD-openssh-portable-overwrite-base-5.2.p1_1,1
. 2009-11-05 09:41:07.581 Using SSH protocol version 2
. 2009-11-05 09:41:07.581 We claim version: SSH-2.0-WinSCP_release_4.2.3
. 2009-11-05 09:41:07.643 Doing Diffie-Hellman group exchange
. 2009-11-05 09:41:07.846 Doing Diffie-Hellman key exchange with hash SHA-1
. 2009-11-05 09:41:08.128 Host key fingerprint is:
. 2009-11-05 09:41:08.128 ssh-dss 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx
. 2009-11-05 09:41:08.128 Initialised AES-256 SDCTR client->server encryption
. 2009-11-05 09:41:08.128 Initialised HMAC-SHA1 client->server MAC algorithm
. 2009-11-05 09:41:08.128 Initialised AES-256 SDCTR server->client encryption
. 2009-11-05 09:41:08.128 Initialised HMAC-SHA1 server->client MAC algorithm
. 2009-11-05 09:41:08.362 Reading private key file "C:\Projects\bin\wilco_private_key.ppk"
! 2009-11-05 09:41:08.362 Using username "mylogin".
. 2009-11-05 09:41:08.425 Offered public key
. 2009-11-05 09:41:08.471 Offer of public key accepted
! 2009-11-05 09:41:08.471 Authenticating with public key "this is my public key"
. 2009-11-05 09:41:08.471 Prompt (2, SSH key passphrase, , Passphrase for key "this is my public key": )
. 2009-11-05 09:41:08.471 Disconnected: Unable to authenticate
-------------------------------------------------------------------------------------

Here is the section of the log when initiating manually from SSIS:

. 2009-11-05 15:43:38.187 Reading private key file "C:\Projects\bin\wilco_private_key.ppk"
. 2009-11-05 15:43:38.187 Pageant is running. Requesting keys.
. 2009-11-05 15:43:38.187 Pageant has 1 SSH-2 keys
. 2009-11-05 15:43:38.187 Pageant key #0 matches configured key file
! 2009-11-05 15:43:38.187 Using username "mylogin".
. 2009-11-05 15:43:38.250 Trying Pageant key #0
! 2009-11-05 15:43:38.328 Authenticating with public key "this is my public key" from agent
. 2009-11-05 15:43:38.375 Sending Pageant's response
. 2009-11-05 15:43:38.484 Access granted
. 2009-11-05 15:43:38.562 Opened channel for session
. 2009-11-05 15:43:38.781 Started a shell/command

---------------------------------------------------------
It's like it can't see that Pageant is running when executing from SQL Agent.

This FAQ may help you. Otherwise post regular session log file (not XML one), your script and output of the scripting console.

I have created an SSIS package containing a script task to connect to an sFtp site using WinSCP. I followed the instructions and used some of the exact code supplied on this site. I am running version 4.2.3 beta so I can utilize the xml logging. The package works beautifully when running from visual studio or manually run from Intregration Services. When I schedule the job through SQL Agent, it runs without error, but doesn't retrieve the files. The log file shows that the connection to the session is made and that is it. Here is the contents of the log when run from SQL Agent job.

<?xml version="1.0" encoding="UTF-8"?>
<session xmlns="https://winscp.net/schema/session/1.0" name="mylogin@ftp2.site.com" start="2009-11-03T13:02:43.487Z">
</session>

In this test instance, I am just trying to do a directory listing by sending the command ls, but this is not being executed or logged. To give you a little more detail, in my script, I am referencing the stored session that I have set up which utilizes a private key. I have Putty Pageant running with the stored password for that key. I am wondering if that is part of my problem. Again, this runs fine from Visual Studio or from Integration Services when run manually, but not when scheduled through SQL Agent job.

One other note, the SQL Agent service account is the same account that I am logged in as when running manually through Visual Studio, so I don't think it is a permissions issue.

Has anyone seen this happen before and/or have any suggestions for getting past this?

Thanks!