SSIS package works in debug but invoking from SQL job fails.
I have a file called C:\SFTP\MyPutScript.txt:-
option batch on
option confirm off
open -privatekey=C:\SFTP\privateprivatekey.ppk sftp://RemoteUsername:MyPassword@RemoteBox
cd "\My Documents"
In the command window I can execute the following successfully:-
C:\>winscp.exe /console /script="C:\SFTP\MyPutScript.txt"
I have built a very simple SSIS package in Visual Studio 2005, just like the example shown in the page:-
I have an Execute Process Task with the following parameters set as shown:-
Executable: C:\Program Files\WinSCP\WinSCP.com
I can run the SSIS package in debug mode and it copies the file to the remote SSH server.
So I created a new SQL Server Agent job and pointed it at the DTSX file produced by Visual Studio. When I execute the SQL Agent job, it fails, with the error:-
"Executed as user: MyPCName\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 18:03:40 Error: 2010-04-28 18:03:40.99 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Program Files\WinSCP\WinSCP.com" "/script="C:\SFTP\MyPutScript.txt"" at "C:\SFTP", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 18:03:40 Finished: 18:03:40 Elapsed: 0.75 seconds. The package execution failed. The step failed."
It looks like the SYSTEM user has trouble working with the WinSCP commands, but I cannot figure it out. I have looked through the FAQs referenced in similar posts here. I notice that "session" and "-hostkey" are mentioned in the script file example given on the example page above; my script does not do it that way. Is that a problem?
I should really appreciate some help.