Topic "Trying to automate winscp from an SQL server DTS script"

Author Message
Leslie 1

Guest


I'm new to this and quite confused!

I want to call winscp from an activex script in a DTS package.

My "example.txt" looks like this:
Code:
option batch on
option confirm off
open user@somewhere.com
cd /in
option transfer binary
put E:\data\test.csv
close
exit


Could anyone tell me how to go about calling winscp from an activex script in an SQL server DTS package?

Thanks
Leslie
Advertisements
Guest




I had the wrong idea there, but now I'm using the "Execute Process Task", which seems more suitable.
I specify the process as:
C:\WinSCP3\WinSCP3.com
and the parameter:
/script=winscp.txt

winscp.txt looks like this:
Code:
option batch on
option confirm off
open user@somewhere
cd /in/
option transfer binary
put C:\WinSCP3\test.txt
close
exit


Now winscp runs but the file is not uploaded. See the lines from the logfile below.
I can upload it sucessfully using the winscp GUI, so any tips as to why it fails using the script?

Thanks
Leslie

--------------------------------------------------------------------------
WinSCP Version 3.7.6 (Build 306) (OS 5.2.3790 Service Pack 1, v.1289)
Login time: 02 November 2005 21:25:33
--------------------------------------------------------------------------
Session name: user@somewhere
Host name: hostname (Port: 22)
User name: user (Password: Yes, Key file: No)
Transfer Protocol: SFTP (SCP)
SSH protocol version: 2; Compression: No
Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
Ciphers: aes,blowfish,3des,WARN,des; Ssh2DES: No
Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
SSH Bugs: -,-,-,-,-,-,-,-
SFTP Bugs: -,-,-
Proxy: none
Return code variable: Autodetect; Lookup user groups: Yes
Shell: default, EOL: 0
Local directory: default, Remote directory: home, Update: No, Cache: Yes
Cache directory changes: Yes, Permanent: Yes
Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
Alias LS: No, Ign LS warn: Yes, Scp1 Comp: No
--------------------------------------------------------------------------
Looking up host "hostname"
Connecting to xxx.xxx.xxx.xxx port 22
Waiting for data timed out, asking user what to do.
Asking user:
Host has not answered for 15 seconds.

Wait for another 15 seconds? Pressing 'Abort' button will close session. ()
Attempt to close connection due to fatal exception:
Terminated by user.
Closing connection.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
Does it work if you execute the very same script regularly (not from DTS)?
_________________
Martin Prikryl
Guest




prikryl wrote:
Does it work if you execute the very same script regularly (not from DTS)?


No it doesn't, and the logfile looks the same if I do this.

I wondered if the problem might be that I don't have read access to the target folder, only write access... would this stop "cd /in" from working?

Leslie
Guest




I also tried using the ini file instead of registry but still the same result.

Leslie
Dave_W

Guest


I also am trying to perform a SFTP transfer from a DTS package. Using another product I can get it to work in DTS design mode, but when it runs as a scheduled job it just hangs. I believe it is to do with the threading model used. Did you get it to work using DTS?
Guest




Has this been solved?

I would guess that the problem is related to the user account that DTS is running as.
It is probable different than the one you normally use. This will mean that any of the
registry settings, etc. used to make the connection aren't working. See the scripting
documentation.
ryebank

Guest


DTS is a weird beast as although it seems like a server sided tool as you access the designer from Enterprise Manager it always actually runs in the client process - so if you run job in designer it is actually executing on your PC not the server so any permissions etc. are based on your own permissions. It is quite common for such jobs then to be scheduled up via SQL Exec and failas they then run at the server under the permissions etc of whatever account is set to run the SQL Exec service.

In my experience you are best designing DTS via terminal services/remote console to the machine that will finally run the job and testing it as the actual user who will finally run it (i.e. account for SQL Exec).
Guest




I do exactly what you are talking about. It is better to create a batch file to process the upload and whatnot and simply call this batch executable from the SQL server. I have had this automation working for about a year now...i use version 3.76 tho because 3.81 had a bug that crashed this. Awesome development job on this prikryl!
GuestDBA

Guest


Try putting the host key in an ini file and use the /ini switch as part of the execute process task. Once I did that, I was able to run the DTS package via a job without any problems.
GuestDBA

Guest


In regards to my last post, it's in response to the problem in getting the DTS package to run via a job. It works with one destination server we have. However, I'm having the same "Host has not answered for 15 seconds" problem as someone else mentioned. I can't figure out how to get around this! Anyone figure it out?
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
GuestDBA wrote:
In regards to my last post, it's in response to the problem in getting the DTS package to run via a job. It works with one destination server we have. However, I'm having the same "Host has not answered for 15 seconds" problem as someone else mentioned. I can't figure out how to get around this! Anyone figure it out?

Are you executing some lenghty command in your script? You may try to set longer timeout then.
_________________
Martin Prikryl
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
For automation please read FAQ.
_________________
Martin Prikryl
Guest




Dave_W wrote:
I also am trying to perform a SFTP transfer from a DTS package. Using another product I can get it to work in DTS design mode, but when it runs as a scheduled job it just hangs. I believe it is to do with the threading model used. Did you get it to work using DTS?


Make sure you are calling the full path of the winscp.exe executable (surround in quotes if there are any spaces in the path) rather than relying on the environmental variable. I've noticed that when I execute the DTS package it picks up the variable. Then I run the scheduled job and it says the program doesn't exist. Go figure!
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
Also see guide to using WinSCP from DTS/SSIS.
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