Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mr4100
Starting Member
6 Posts |
 Posted - 2007-09-25 : 09:33:09
|
| Hi,I have a job that runs a ssis package but the job will not execute the second step which uses a 3rd party software to download a file from a sftp server. My logon is in the admin group, I am using Local System as the logon service for sqlserver agent, my account has sysadmin server role in sqlserver, I added my account to logon as a service and batch job in local services, my account created the package. The package will execute fine using dtexec and windows task scheduler. I've even tried scheduling the job with a proxy and that won't work either. i'm running xp prof. Any other ideas? Here is the error:Microsoft (R) SQL Server Execute Package UtilityVersion 9.00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 9:13:14 AMError: 2007-09-25 09:13:17.62 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Documents and Settings\mroush\Desktop\SQL 2005 DMS project-Prod\WinSCP3\WinSCP3.com" "/script="C:\Documents and Settings\mroush\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\sftpscript.txt"" at "", The process exit code was "1" while the expected was "0".End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 9:13:14 AMFinished: 9:13:17 AMElapsed: 2.875 secondsthanks,mike |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-25 : 23:19:26
|
| Enable package logging so you can get detailed reason on why the step fails. |
 |
|
|
mr4100
Starting Member
6 Posts |
Posted - 2007-09-26 : 10:04:57
|
| It's executing the package using account NT AUTHORITY\SYSTEM. I just don't know how to go about changing it to my account? I tried scheduling it using a proxy with my account but no luck. I gave NT AUTHORITY\SYSTEM sysadmin role and sqlagentuser role on the server and no luck. i've tried chaning the logon to my account and no luck either. i'll keep trying, maybe i'm doing things out of sync. oh i am still getting the same error as above it just tells who is executing the package which did help when i enabled package logging.thanks, |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-26 : 23:11:17
|
| Try start sql agent with your account. |
|
|
|
mr4100
Starting Member
6 Posts |
Posted - 2007-09-27 : 11:42:07
|
| I changed it to logon as my account and am able to schedule the job to run and download the file to my local hard drive. If I change it to download to a server on the network, it fails. I think either the server needs to grant the program that I am using to download the file permission to read or write or the program I am using needs to grant the sqlagent permission to use it? I am executing the job under the sqlagent service account and not a proxy. Had no luck using a proxy. Any other ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-28 : 00:32:40
|
| Sql agent service account needs permission on remote location used in the package. |
|
|
|
mr4100
Starting Member
6 Posts |
Posted - 2007-09-28 : 09:06:44
|
| Our Lan Admin won't allow that. If I'm using my account to logon to the sqlserver agent, wouldn't it be using my credentials on the server when the job executes or is the sql agent service account a different instance? I tried to use a proxy under my account but it won't work. I use my account for the proxy and my account has sysadmin role and under usermapping on the msdb I have all db role memberships checked? Am I missing something? I appreciate your help on this! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-28 : 22:32:30
|
| What do you mean 'using my account to logon to the sqlserver agent'? Start sql agent with your domain account? |
|
|
|
|
|
|
|
|
SQL Server Agent not....