April 29, 2015 at 10:26 am
jasona.work (4/29/2015)
Brandie,You're going to laugh at this one...
Is going to simply echo your commands into your txt file...
It should still write (or try to, at least) the output of the commands to the txt file, and actually run them.
#HEADDESK.
YEP. Now I can see the responses. And it's tell me that it doesn't understand a thing my batch file is trying to do, which is understandable since I'm calling the batch file outside of WinSCP. Now I need to see what happens when I execute it within the WinSCP context.
Thanks, all. I may or may not be posting more as I get further into this.
April 29, 2015 at 10:33 am
GAH. Now I get nothing at all in the log file. The only upside was I could see the issues in the command prompt.
SIGH. Why can't this be easy?
April 29, 2015 at 11:00 am
If the commands being run don't return an output to the command prompt window, there's going to be nothing to put in the log file.
Such as:
cd /Folder1/sub1/sub2/
Won't return any output if successful, so nothing to log.
If you really want to log, you're getting into possibly Powershell territory.
I can't speak to logging from the WinSCP portion, though.
What I would suggest, and it helps that this is a small batch file, is manually run each line, one-by-one in a command prompt to verify they work. I'm somewhat dubious that the "PUT" is going to work, unless the "OPEN SFTP" kicks the command prompt into WinSCP.
April 29, 2015 at 11:18 am
Brandie, a word of encouragement if I may. Your batch file is creating your WinSCP script file. You then invoke it by calling the WinSCP executable from the batch file, right? You're on the right track here.
1. You just have to make the WinSCP script file to do what you want it to do when invoked by WinSCP. Take a look at your FTPLog.txt file. Does it look right? If not, make it right and then move on.
2. The next step is to make sure your WinSCP command uses the WinSCP script file and does what you want it to do. Once you get it working at a command line, move on.
3. The next step is to look at your batch file. You have it creating a valid WinSCP script from step 1 above. You know the command to invoke it from step 2 above. Put them together in the batch file and make sure it works.
The next step will be to introduce the variables like having the files on other (non-local) drives, etc.
April 30, 2015 at 5:13 am
Ed Wagner (4/29/2015)
Brandie, a word of encouragement if I may. Your batch file is creating your WinSCP script file. You then invoke it by calling the WinSCP executable from the batch file, right? You're on the right track here.1. You just have to make the WinSCP script file to do what you want it to do when invoked by WinSCP. Take a look at your FTPLog.txt file. Does it look right? If not, make it right and then move on.
2. The next step is to make sure your WinSCP command uses the WinSCP script file and does what you want it to do. Once you get it working at a command line, move on.
3. The next step is to look at your batch file. You have it creating a valid WinSCP script from step 1 above. You know the command to invoke it from step 2 above. Put them together in the batch file and make sure it works.
The next step will be to introduce the variables like having the files on other (non-local) drives, etc.
This is really really headache inducing.
My bat file contains the following:
'C:\Program Files (x86)\WinSCP\winscp.exe' /script=\\MyNASShare\SFTPFile.txt
And I'm getting the following response:
The filename, directory name, or volume label syntax is incorrect.
The batch file just isn't working. Powershell is my next step if I can't get this to work.
April 30, 2015 at 5:38 am
Brandie, I think your batch file is almost there, just a couple more tweaks and it should go.
That being said, I'm 90% sure that DOS doesn't like single quotes around path / programs, it prefers double quotes.
So instead of:
'C:\Program Files (x86)\WinSCP\winscp.exe' /script=\\MyNASShare\SFTPFile.txt
go with
"C:\Program Files (x86)\WinSCP\winscp.exe" /script=\\MyNASShare\SFTPFile.txt
Possibly also, at least for the initial test (unless you are 100% sure that the SSIS proxy / SQL Agent account has access) move the winscp script file local to the server, rather than on a network share.
Basically, simplify the heck out of everything, get it working, then start setting things up the way you want in the end.
April 30, 2015 at 6:02 am
jasona.work (4/30/2015)
I'm 90% sure that DOS doesn't like single quotes around path / programs, it prefers double quotes.So instead of:
'C:\Program Files (x86)\WinSCP\winscp.exe' /script=\\MyNASShare\SFTPFile.txtgo with
"C:\Program Files (x86)\WinSCP\winscp.exe" /script=\\MyNASShare\SFTPFile.txt
Perfect, Jason, thank you. I'm 100% sure that's a problem.
jasona.work (4/30/2015)
Possibly also, at least for the initial test (unless you are 100% sure that the SSIS proxy / SQL Agent account has access) move the winscp script file local to the server, rather than on a network share.Basically, simplify the heck out of everything, get it working, then start setting things up the way you want in the end.
I couldn't agree more.
You're almost there, Brandie.
April 30, 2015 at 6:07 am
jasona.work (4/30/2015)
Brandie, I think your batch file is almost there, just a couple more tweaks and it should go.That being said, I'm 90% sure that DOS doesn't like single quotes around path / programs, it prefers double quotes.
So instead of:
'C:\Program Files (x86)\WinSCP\winscp.exe' /script=\\MyNASShare\SFTPFile.txtgo with
"C:\Program Files (x86)\WinSCP\winscp.exe" /script=\\MyNASShare\SFTPFile.txtPossibly also, at least for the initial test (unless you are 100% sure that the SSIS proxy / SQL Agent account has access) move the winscp script file local to the server, rather than on a network share.
Basically, simplify the heck out of everything, get it working, then start setting things up the way you want in the end.
The double-quotes was the first thing I tried. DOS took it as a literal string, not the path to the file. But thank you for the recommendation.
I went ahead and did the Powershell script. I got it working in the first attempt, except I haven't quote figured out how to redirect the Write-Output.
David, how do I fix the below code to point to a specifically named log file?
# Print results
foreach ($transfer in $transferResult.Transfers)
{
Write-Output ("Upload of {0} succeeded" -f $transfer.FileName)
}
Do I add a sessionOptions.FileName to the code to define it?
April 30, 2015 at 6:40 am
Try directing the pipeline to the Out-File cmdlet
Not actually tried it but something like this
Write-Output ("Upload of {0} succeeded" -f $transfer.FileName) | Out-File c:\output\test.txt
*Edited*
You will need to include -append
Far away is close at hand in the images of elsewhere.
Anon.
April 30, 2015 at 7:23 am
And now it is working.
Everyone has been incredibly helpful. Thank you. Here's the file Powershell script that I'm using.
$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 = "LinuxServer"
$sessionOptions.UserName = "MyName"
$sessionOptions.Password = "MyPwd"
$sessionOptions.SshHostKeyFingerprint = "ssh-rsa xxxx xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
$remotePath = "/directory/sub1/sub2/"
$localPath = "\\MyNASShare\"
# 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\MyFile.txt", "$remotePath\MyFile", $FALSE, $transferOptions)
# Throw on any error
$transferResult.Check()
# Get date for log
$LogTime = Get-Date -Format "MM-dd-yyyy_hh:mm:ss"
# Print results
foreach ($transfer in $transferResult.Transfers)
{
Write-Output ($LogTime + " Upload of {0} succeeded" -f $transfer.FileName) | Out-File -FilePath \\MyNASShare\SFTPLog.txt -Append
}
# Disconnect, clean up
if (!($session))
{
$session.Dispose()
}
My EXECUTE PROCESS task is set up with
EXECUTABLE C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
ARGUMENTS -windowstyle Hidden -ExecutionPolicy ByPass -Command \\MyNASShare\SFTPFile.ps1
Everything else is defaulted. Works WONDERFULLY. I also have a task that zips up the SFTPLog.txt with the file that was sent and archives them both off for historical purposes, but that is a part of the original package (is not a new thing). Even using the -APPEND, if the log file does not exist, the powershell script will create it.
"Everything is awesome!"
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy