SQL Server scheduled job fails

Advertisement

mellomel70
Joined:
Posts:
2

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!

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: SQL Server scheduled job fails

Please post a full log file showing the problem.
_________________
Martin Prikryl

Reply with quote

mellomel70
Joined:
Posts:
2

Request for log file

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.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: Request for log file

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?
_________________
Martin Prikryl

Reply with quote

mjjohn04
Guest

SSIS Package failes in SQL Agent but runs in debug from IDE

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

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: SSIS Package failes in SQL Agent but runs in debug from IDE

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.

You will find my address (if you log in) in my forum profile.

Reply with quote

viveksingh16
Joined:
Posts:
2
Location:
japan

viveksingh16

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

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: viveksingh16

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?

Reply with quote

berfiszman
Guest

Error Copying File

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.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: Error Copying File

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?
_________________
Martin Prikryl

Reply with quote

Advertisement

You can post new topics in this forum