scheduling SFTP WinSCP SSIS package through SQL Agent. -
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!
<?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!