Forum » Scripting / Automation »

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

Advertisement

jamescollett
Joined:
Posts:
11
Location:
UK

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

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.

Reply with quote

Advertisement

jamescollett
Joined:
Posts:
11
Location:
UK

Re: SSIS package works in debug but invoking from SQL job fa

I have but I do not understand it properly. :cry:

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.

Reply with quote

jarnold4112
Guest

Re: SSIS package works in debug but invoking from SQL job fa

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.

Reply with quote

Advertisement

martin◆
Site Admin
martin avatar
Joined:
Posts:
41,405
Location:
Prague, Czechia

Re: SSIS package works in debug but invoking from SQL job fa

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

Reply with quote

Gerald
Guest

Re: SSIS package works in debug but invoking from SQL job fa

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

Reply with quote

Guest

Re: SSIS package works in debug but invoking from SQL job fa

I have deleted too much in the second log-file - the session line looks like: 
Session name: SESSIONNAME (Ad-Hoc session)

Reply with quote

martin◆
Site Admin
martin avatar

Re: SSIS package works in debug but invoking from SQL job fa

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?

Reply with quote

Advertisement

Gerald
Guest

Re: SSIS package works in debug but invoking from SQL job fa

martin 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).

Reply with quote

Gerald
Guest

Re: SSIS package works in debug but invoking from SQL job fa

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.

Reply with quote

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

Reply with quote

Advertisement

martin◆
Site Admin
martin avatar
Joined:
Posts:
41,405
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: 
. 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

Reply with quote

Advertisement

You can post new topics in this forum

Documentation

Support

Associations

Follow Us