April 29, 2015 at 8:44 am
All,
I'm trying to call WinSCP in a SSIS Execute Process Task using a .bat file to automate. SSIS is returning a generic failure error message ("Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "\\MyServer\C$\Program Files (x86)\WinSCP\WinSCP.exe" "-script=\\MyNASShare\WinSCPFTP.bat " at "", The process exit code was "1" while the expected was "0"."). So now I'm trying to run the .bat file by itself. Unfortunately, the command prompt rushes by so fast that I can't see what the server is doing.
I can't find anything on the WinSCP site that indicates how to slow down the .bat file processing or log the remote server responses. I do see how to use the /log switch when using the interactive command line console, but that's not what I want to do.
Does anyone have any thoughts?
April 29, 2015 at 8:53 am
April 29, 2015 at 8:55 am
I can't help you at all with the SSIS part, so I'll focus in on the WinSCP part. It's been a while for me, but I'll give it a try.
1. Do you have your named connection already defined in WinSCP? Is it the same name as defined in your batch file?
2. If you remote in to the server and open a command prompt, can you run the batch file by itself? You should be able to see the results in the same window.
3. This is where my ignorance of SSIS comes in. If you're using a shell out to WinSCP, does the network user being in use by the shell process have permission to both the server and the share where your batch file is located? In your OP, you call them \\MyServer\C$ and \\MyNASShare. If you're using a local user for the shell, I don't think it'll have permission to anything outside the server.
I guess I would try to first make it as simple as possible by having everything run locally. Then, as you get it to work, fill in more of the pieces you need so you know which point in the process is giving you trouble.
I hope this helps and I'm not too far off. Like I said, it's been a while.
April 29, 2015 at 8:55 am
This is what I use, SFTP Task for SSIS
😎
April 29, 2015 at 9:00 am
You know how when you run a .bat file that uses command line stuff, a prompt opens up and processes the code?
That part is what I'm trying to pause. I need to see what the .bat file is doing, and what responses its getting from the remote server. Nothing in the WinSCP site tells me how to pause or wait the window. I tried using the windows command prompts of "PAUSE 10" etc and they aren't work.
I really really need that window to stay open and not go away so I can read the responses.
Any thoughts on how to accomplish that?
April 29, 2015 at 9:00 am
As a way to temporarily troubleshoot the batch file, without jumping through dozens of hoops, try adding pauses as strategic points (or between each command.)
Yes, you'll need to hit a key to continue, but that's the whole point, as you'll be able to see what's going on.
So, an example would be:
@echo off
{do something here}
pause
{do next thing}
pause
Rinse and repeat.
If you've only got a few lines, one pause at the end might do it. Now, this won't help for whatever might be going on inside WinSCP when it's doing it's work, but if it's throwing an error out to the command prompt, you should catch it this way.
April 29, 2015 at 9:03 am
jasona.work (4/29/2015)
As a way to temporarily troubleshoot the batch file, without jumping through dozens of hoops, try adding pauses as strategic points (or between each command.)Yes, you'll need to hit a key to continue, but that's the whole point, as you'll be able to see what's going on.
So, an example would be:
@echo off{do something here}
pause
{do next thing}
pause
Rinse and repeat.
If you've only got a few lines, one pause at the end might do it. Now, this won't help for whatever might be going on inside WinSCP when it's doing it's work, but if it's throwing an error out to the command prompt, you should catch it this way.
That was it. Apparently I was using SLEEP, not pause. Thanks, Jason.
April 29, 2015 at 9:04 am
Brandie Tarvin (4/29/2015)
You know how when you run a .bat file that uses command line stuff, a prompt opens up and processes the code?That part is what I'm trying to pause. I need to see what the .bat file is doing, and what responses its getting from the remote server. Nothing in the WinSCP site tells me how to pause or wait the window. I tried using the windows command prompts of "PAUSE 10" etc and they aren't work.
I really really need that window to stay open and not go away so I can read the responses.
Any thoughts on how to accomplish that?
You don't get to specify a pause duration in DOS. The pause command waits for you to press a key.
However, does your batch file open separate window for the WinSCP execution? If not, I don't get it. If so, do you have to start a new DOS window? You could put a DOS pause command at the end of your WinSCP session after the WinSCP session has terminated and I'd think it would keep the window on the screen.
April 29, 2015 at 9:07 am
Hrm... I'm not getting any response in the command prompt window. I'm just seeing the echo of my commands, nothing else.
Any more thoughts?
EDITED to add .bat file code.
rem Automatically abort script on errors
echo option batch abort > \\MyNASShare\FTPLog.txt
rem Disable overwrite confirmations that conflict with the previous
echo option confirm off >> \\MyNASShare\FTPLog.txt
rem Connect to SFTP server
echo open sftp://Mylogin:MyPwd@LinuxServerName.com -hostkey="ssh-rsa XXXX xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx" >> \\MyNASShare\FTPLog.txt
rem Change directory
echo cd /Folder1/sub1/sub2/ >> \\MyNASShare\FTPLog.txt
rem Upload file
echo put \\MyNASShare\FileName.txt FileName >> \\MyNASShare\FTPLog.txt
pause 200
rem Disconnect
echo close >> \\MyNASShare\FTPLog.txt
April 29, 2015 at 9:12 am
Ed Wagner (4/29/2015)
Brandie Tarvin (4/29/2015)
You know how when you run a .bat file that uses command line stuff, a prompt opens up and processes the code?That part is what I'm trying to pause. I need to see what the .bat file is doing, and what responses its getting from the remote server. Nothing in the WinSCP site tells me how to pause or wait the window. I tried using the windows command prompts of "PAUSE 10" etc and they aren't work.
I really really need that window to stay open and not go away so I can read the responses.
Any thoughts on how to accomplish that?
You don't get to specify a pause duration in DOS. The pause command waits for you to press a key.
However, does your batch file open separate window for the WinSCP execution?
Yes, it does. But it flashes by too quickly for me to read it, hence the original question. And now I have a new question because the window doesn't show what I expect to see. See my last post above.
April 29, 2015 at 9:16 am
I run WinSCP via Powershell script
Ooops! you're running is SSIS, ignore me :blush:
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2015 at 9:31 am
David Burrows (4/29/2015)
I run WinSCP via Powershell scriptOoops! you're running is SSIS, ignore me :blush:
David, my issues are with WinSCP period. So any advice you can give me on the WinSCP code would be helpful. Please, speak up if you know how to echo the responses from the remote server.
April 29, 2015 at 9:37 am
So you have the batch file creating the WinSCP script in a separate file called FTPLog.txt. You then have a line in the batch file to invoke WinSCP and tell it to use FTPLog.txt. Can you open a separate DOS window and execute that line to invoke WinSCP?
April 29, 2015 at 9:48 am
Brandie Tarvin (4/29/2015)
, my issues are with WinSCP period. So any advice you can give me on the WinSCP code would be helpful. Please, speak up if you know how to echo the responses from the remote server.
Not sure if this will help but this is the powershell script I use within sqlagent job which I adapted from various web sources for my own use
It uses WinSCp .NET library which is downloadable
The WinSCP website has details for transfer result from putfiles which I think includes errors.
It is possible to execute a powershell script file within SSIS which could output logging info
$erroractionpreference = "Stop"
# Load WinSCP .NET assembly
[Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\WinScp\WinSCPnet.dll")
# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions
$sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
$sessionOptions.HostName = "nnn.nnn.nnn.nnn"
$sessionOptions.UserName = "name"
$sessionOptions.Password = "password"
$sessionOptions.SshHostKeyFingerprint = "ssh-dss 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
$remotePath = "/remotedir/*"
$localPath = "localdir"
# Create Session
$session = New-Object WinSCP.Session
# Connect
$session.Open($sessionOptions)
# Upload files
$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.TransferMode = [WinSCP.TransferMode]::Ascii
$transferResult = $session.PutFiles("$localPath\*.txt", "$remotePath", $FALSE, $transferOptions)
# Throw on any error
$transferResult.Check()
# Print results
foreach ($transfer in $transferResult.Transfers)
{
Write-Output ("Upload of {0} succeeded" -f $transfer.FileName)
}
# Disconnect, clean up
if (!($session))
{
$session.Dispose()
}
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2015 at 9:52 am
Brandie,
You're going to laugh at this one...
Your batch file:
rem Automatically abort script on errors
echo option batch abort > \\MyNASShare\FTPLog.txt
rem Disable overwrite confirmations that conflict with the previous
echo option confirm off >> \\MyNASShare\FTPLog.txt
rem Connect to SFTP server
echo open sftp://Mylogin:MyPwd@LinuxServerName.com -hostkey="ssh-rsa XXXX xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx" >> \\MyNASShare\FTPLog.txt
rem Change directory
echo cd /Folder1/sub1/sub2/ >> \\MyNASShare\FTPLog.txt
rem Upload file
echo put \\MyNASShare\FileName.txt FileName >> \\MyNASShare\FTPLog.txt
pause 200
rem Disconnect
echo close >> \\MyNASShare\FTPLog.txt
Is going to simply echo your commands into your txt file...
Try this:
rem Automatically abort script on errors
option batch abort > \\MyNASShare\FTPLog.txt
rem Disable overwrite confirmations that conflict with the previous
option confirm off >> \\MyNASShare\FTPLog.txt
rem Connect to SFTP server
open sftp://Mylogin:MyPwd@LinuxServerName.com -hostkey="ssh-rsa XXXX xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx" >> \\MyNASShare\FTPLog.txt
rem Change directory
cd /Folder1/sub1/sub2/ >> \\MyNASShare\FTPLog.txt
rem Upload file
put \\MyNASShare\FileName.txt FileName >> \\MyNASShare\FTPLog.txt
pause
rem Disconnect
close >> \\MyNASShare\FTPLog.txt
It should still write (or try to, at least) the output of the commands to the txt file, and actually run them.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply