Topic "SSIS package works in debug but invoking from SQL job fails."

Author Message
jamescollett
[View user's profile]

Joined: 2010-04-28
Posts: 11
Location: UK
I am in the process of trying to automate some file copies using SFTP and SSIS packages. The problem I have is when I instal an SSIS package as a scheduled job within SQL Server Agent (SQL Server 2005).

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
lcd C:\SFTP
cd "\My Documents"
put ToServer.txt
exit

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:-

https://winscp.net/eng/docs/guide_ssis

I have an Execute Process Task with the following parameters set as shown:-

Executable: C:\Program Files\WinSCP\WinSCP.com
Arguments: /script="C:\SFTP\MyPutScript.txt"
WorkingDirectory: C:\SFTP

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.
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
Have you read this FAQ?
_________________
Martin Prikryl
jamescollett
[View user's profile]

Joined: 2010-04-28
Posts: 11
Location: UK
I have but I do not understand it properly. Crying or Very sad

I have not done anything with configuration storage - I did not appreciate there was something to worry about in this area.

Nor do I do anything with host keys or fingerprints.

The information I followed to build my scripts did not cover these things. I guess I should look into them.
jamescollett
[View user's profile]

Joined: 2010-04-28
Posts: 11
Location: UK
I have since managed to get this to work. Not sure exactly what fixed it but it involved changing the user underwhich the various SQL Server services run.
jarnold4112

Guest


We recently were experiencing the same problem and discovered that it was in fact a problem with the storage location. Changing the storage location to .ini in preferences, then exporting to an .ini file in the same directory as the WinSCP executable we were able to solve the problem. No other changes were necessary.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
jarnold4112 wrote:
We recently were experiencing the same problem and discovered that it was in fact a problem with the storage location. Changing the storage location to .ini in preferences, then exporting to an .ini file in the same directory as the WinSCP executable we were able to solve the problem. No other changes were necessary.

That's exactly from FAQ referenced above is about
https://winscp.net/eng/docs/faq_scheduler
_________________
Martin Prikryl
Gerald

Guest


prikryl wrote:
jarnold4112 wrote:
We recently were experiencing the same problem and discovered that it was in fact a problem with the storage location. Changing the storage location to .ini in preferences, then exporting to an .ini file in the same directory as the WinSCP executable we were able to solve the problem. No other changes were necessary.

That's exactly from FAQ referenced above is about
https://winscp.net/eng/docs/faq_scheduler


I'm running a SQL Server Agent Job which calls an SSIS package calling WinSCP.exe per command line. This job is running in the user's context who created also the WinSCP session for connecting. Most of the time the job is working, but randomly it cannot connect to the SFTP-Server.

If it works the log file will look like:
Code:
--------------------------------------------------------------------------
WinSCP Version 4.3.3 (Build 1340) (OS 5.2.3790 Service Pack 2)
Login time: ...
--------------------------------------------------------------------------
Session name: SESSIONNAME
Host name: HOSTNAME (Port: 22)
User name: USERNAME (Password: Yes, Key file: No)
...
--------------------------------------
Looking up host HOSTNAME


If it does not work the log file will look like:
Code:
--------------------------------------------------------------------------
WinSCP Version 4.3.3 (Build 1340) (OS 5.2.3790 Service Pack 2)
Configuration: HKEY_CURRENT_USER\Software\Martin Prikryl\WinSCP 2\
Login time: ...
--------------------------------------------------------------------------
Session name: SESSIONNAME
Host name: SESSIONNAME (Port: 22)
User name: (Password: No, Key file: No)
...
--------------------------------------
Looking up host SESSIONNAME


Any ideas what could be the problem?
I'm running WinSCP.exe instead of .com - might this be the cause of the troubles?

Thanks in advance and best regards,
Gerald
Guest




I have deleted too much in the second log-file - the session line looks like:
Code:
Session name: SESSIONNAME (Ad-Hoc session)
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
In the second case WinSCP did not find the session SESSIONAME, so it used SESSIONNAME as a hostname. Why is that happening I do not know. Aer you sure WinSCP was running under the same account both times?
_________________
Martin Prikryl
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
This helped me to realize that it is worth including local account into the log:
https://winscp.net/tracker/show_bug.cgi?id=709
_________________
Martin Prikryl
Gerald

Guest


prikryl wrote:
In the second case WinSCP did not find the session SESSIONAME, so it used SESSIONNAME as a hostname. Why is that happening I do not know. Aer you sure WinSCP was running under the same account both times?


I'm quite sure because it is the same job for months which is running automatically at 3am. I can't recognize any pattern. It works for several days, then not for 1-3 days and so on.

Thank you for adding the enhancement (Bug 709).
Gerald

Guest


Just one more thing which might be important: the job is running in the context of a local user and not of a domain user.
Gerald

Guest


Hi Martin,

I have installed 5.0.2 beta at the customer a few days ago. It was working well, but last night I again had this strange error. The log file looks like:
Code:

<?xml version="1.0" encoding="UTF-8"?>
<session xmlns="http://winscp.net/schema/session/1.0" name="SESSION" start="2011-10-04T00:00:07.310Z">
  <result success="false">
    <message>Host does not exist</message>
  </result>
</session>
---
. 2011-10-04 03:00:07.310 WinSCP Version 5.0.2 (Build 1456) (OS 5.2.3790 Service Pack 2)
. 2011-10-04 03:00:07.310 Configuration: HKEY_CURRENT_USER\Software\Martin Prikryl\WinSCP 2\
. 2011-10-04 03:00:07.310 Local account: DOMAIN\User
. 2011-10-04 03:00:07.310 Login time: Dienstag, 04. Oktober 2011 03:00:07
. 2011-10-04 03:00:07.310 --------------------------------------------------------------------------
. 2011-10-04 03:00:07.310 Session name: SESSION (Ad-Hoc session)
. 2011-10-04 03:00:07.310 Host name: SESSION (Port: 22)
. 2011-10-04 03:00:07.310 User name:  (Password: No, Key file: No)
. 2011-10-04 03:00:07.310 Tunnel: No
. 2011-10-04 03:00:07.310 Transfer Protocol: SFTP (SCP)
. 2011-10-04 03:00:07.310 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2011-10-04 03:00:07.310 Proxy: none
. 2011-10-04 03:00:07.310 SSH protocol version: 2; Compression: No
. 2011-10-04 03:00:07.310 Bypass authentication: No
. 2011-10-04 03:00:07.310 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2011-10-04 03:00:07.310 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2011-10-04 03:00:07.310 SSH Bugs: -,-,-,-,-,-,-,-,-
. 2011-10-04 03:00:07.310 SFTP Bugs: -,-
. 2011-10-04 03:00:07.310 Return code variable: Autodetect; Lookup user groups: Yes
. 2011-10-04 03:00:07.310 Shell: default
. 2011-10-04 03:00:07.310 EOL: 0, UTF: 2
. 2011-10-04 03:00:07.310 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2011-10-04 03:00:07.310 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2011-10-04 03:00:07.310 Local directory: default, Remote directory: home, Update: No, Cache: Yes
. 2011-10-04 03:00:07.310 Cache directory changes: Yes, Permanent: Yes
. 2011-10-04 03:00:07.310 DST mode: 1
. 2011-10-04 03:00:07.310 --------------------------------------------------------------------------
. 2011-10-04 03:00:07.310 Looking up host "SESSION"


If it works the log file looks nearly the same (even the configuration file location is ok) - the only difference is:
Code:

. 2011-10-04 03:00:07.310 Session name: SESSION (Ad-Hoc session)
. 2011-10-04 03:00:07.310 Host name: HOSTNAME(Port: 22)


Any ideas?

Thanks,
Gerald
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
Gerald wrote:
If it works the log file looks nearly the same (even the configuration file location is ok) - the only difference is:
Code:

. 2011-10-04 03:00:07.310 Session name: SESSION (Ad-Hoc session)
. 2011-10-04 03:00:07.310 Host name: HOSTNAME(Port: 22)

Are you sure, there's "(Ad-Hoc session)" (in the log where it works)?

Please read this FAQ:
https://winscp.net/eng/docs/faq_scheduler
_________________
Martin Prikryl
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License