Trying to automate winscp from an SQL server DTS script

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:

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

Thanks
Leslie

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:

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.

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

martin 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

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

Leslie

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?

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.

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

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!

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.

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?

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.

For automation please read FAQ.

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!

Also see guide to using WinSCP from DTS/SSIS.