SQL Server scheduled job fails

Hi all,

I believe I'm running into a problem similar to the one described in this forum post:

https://winscp.net/forum/viewtopic.php?t=7538

However, I've followed the instructions in that post and my problem persists. Here are the details:

Our Environment:
W2003 SP2
SQL Server 2000
WinSCP 4.1.9 (Build 416)

I am running a SQL Server DTS package which includes an Execute Process Task. The Execute Process Task calls the Win32 process D:\WinSCP\WinSCP.exe (WinSCP is installed at the root of D: on this machine). The Win32 process is passed 3 parameters: /console /script=Upload.bat /log=myerror.txt. Upload.bat does the following:

option batch on
option confirm off
open <ourusername:ourpassword@ourhost>
cd /Loader
option transfer binary
put ADSync.csv
close
exit

ADSync.csv is a file that the DTS package creates prior to running the Execute Process Task. The file is created without a problem. And, the Execute Process Task will run if I execute the DTS package manually, or if I run the WinSCP.exe with the appropriate parameters in a command line. However, when the DTS package is scheduled as a job, it fails. It doesn't create the myerror.txt logfile, so it's difficult to know what the problem is. As outlined in the above-referenced post (https://winscp.net/forum/viewtopic.php?t=7538), I have changed the WinSCP storage configuration to the INI file, the INI file is in the WinSCP directory, and our DBA assures me that the SQL agent has access to the WinSCP directory (our SQL agent has admin rights).

What could be causing this behavior?

Thanks very much!

Please post a full log file showing the problem.

Unfortunately, the log file I specify, HubbardOne.log does not get created. The only message I get is in the Windows application event viewer. That message is a warning only and reads:

SQL Server Scheduled Job 'HubbardOneData_test' (0xA41EB804303BB34BA0C62B5B67BD38E9) - Status: Failed - Invoked on: 2009-11-30 11:42:30 - Message: The job failed. The Job was invoked by User <myuserid>. The last step to run was step 1 (HubbardOneData_test).

which doesn't really tell me anything I don't already know.

Have you tried to specify full path to a log file, to make sure it is being written to path, where your account has write permissions?

I created a SSIS Package that uploads file to sFTP. When i execute through VS it uploads ok. When i create a package and place it in Sql agent it fails with the error below. I have other jobs using the same scripts and works fine.

01/28/2013 10:37:18,fileupload,Error,0,dcsql01,fileupload,(Job outcome),,The job failed. The Job was invoked by User dcsql01\Administrator. The last step to run was step 1

(Transfer).,00:00:04,0,0,,,,0
01/28/2013 10:37:18,fileupload,Error,1,dcsql01,fileupload,Transfer,,Executed as user: dcsql01\SQL-service. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit

Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:37:19 AM Error: 2013-01-28 10:37:22.52 Code: 0x00000000 Source: Script Task Description: Error

when using WinSCP to upload files: WinSCP.SessionLocalException: WinSCP process terminated with exit code 3 and output ""<c/> without creating a log file. at WinSCP.Session.Open

(SessionOptions sessionOptions) at ST_5a30686e70c04c5a8a93729fd90b8c79.csproj.ScriptMain.Main() End Error Error: 2013-01-28 10:37:22.52 Code: 0x00000008 Source: Script Task

Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:37:19 AM Finished: 10:37:22 AM Elapsed: 3.391

seconds. The package execution failed. The step failed.,00:00:04,0,0,,,,0

mjjohn04 wrote:

I created a SSIS Package that uploads file to sFTP. When i execute through VS it uploads ok. When i create a package and place it in Sql agent it fails with the error below. I have other jobs using the same scripts and works fine.

Can you send me an email, so I can send you back a debug version of WinSCP to track the problem? Please include link back to this topic in your email. Also note in this topic that you have sent the email. Thanks.

Hi prikryl ,

Any Update on this Issue.
I am also facing same issue.

Could you please let me know what did you do?.

Thanks and Regards,
VIvek

viveksingh16 wrote:

Hi prikryl ,

Any Update on this Issue.
I am also facing same issue.

Could you please let me know what did you do?.

Thanks and Regards,
VIvek

Can you please start a new thread and provide some details, version of WinSCP, logs or anything?

Hi, I get an error when wanting to copy a file. The user has full control permissions to the folder.

Message

Executed as user: xx\XA8987. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:04:01 PM Error: 2015-03-02 16:04:05.10 Code: 0x00000008 Source: Comprimir Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:04:01 PM Finished: 4:04:05 PM Elapsed: 3.775 seconds. The package execution failed. The step failed.

Tks.

berfiszman wrote:

Hi, I get an error when wanting to copy a file. The user has full control permissions to the folder.

Message

Executed as user: xx\XA8987. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:04:01 PM Error: 2015-03-02 16:04:05.10 Code: 0x00000008 Source: Comprimir Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:04:01 PM Finished: 4:04:05 PM Elapsed: 3.775 seconds. The package execution failed. The step failed.

The same: Can you please start a new thread and provide some details, version of WinSCP, logs or anything?

Executed as client: xx\XA8987. Microsoft (R) SQL Server Execute Bundle Utility Variant 10.50.1600.1 for 32-bit Copyright (C) Microsoft Company 2010. All rights held. Begun: 4:04:01 PM Blunder: 2015-03-02 16:04:05.10 Code: 0x00000008 Source: Comprimir Depiction: The content restored a disappointment result. End Mistake DTExec: The bundle execution returned DTSER_FAILURE (1). Begun: 4:04:01 PM Completed: 4:04:05 PM Passed: 3.775 seconds. The bundle execution fizzled. The progression fizzled.