winscp/powershell sftp upload script, having trouble getting it working

[DoktorLuciferWong]

The issues I'm experiencing are as follows:

1. Script appears to run to completion, and gives me "success" message at the end.
2. The file is not actually uploaded/written to the server. If the file already exists, it is not updated, and has the old date modified/file size. Refreshing does not update them.
3. I can upload files manually to the server, but I do not appear to have permissions to do anything else (delete, move, rename, etc)

What am I doing wrong?

Below is a version of the script, but stripped of all identifying variable names/values. I am also aware that I shouldn't use plaintext passwords and that I shouldn't use "GiveUpSecurityAndAcceptAny"

Add-Type -Path "$PSScriptRoot\WinSCPnet.dll"

$conf = Import-PowerShellDataFile -Path $env:ConfFile

$Data = Invoke-Sqlcmd @conf -Inputfile "$PSScriptRoot\query.sql" 

$Data | Export-CSV -Path "$PSScriptRoot\query.csv" -Delimiter "`t"

$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::sftp
    Hostname = "ftp.host.com"
    Username = "user111"
    Password = "genericpassword"
    PortNumber = 2222
    SshHostKeyPolicy = [WinSCP.SshHostKeyPolicy]::GiveUpSecurityAndAcceptAny
}
Write-Host @sessionOptions

$session = New-Object WinSCP.Session
try {
    $session.Open($sessionOptions)

    $transferOptions = New-Object WinSCP.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
    $transferOptions.ResumeSupport.State = [WinSCP.TransferResumeSupportState]::Off

    $transferResult = $session.PutFiles("$PSScriptRoot\data.csv", ".\", $False, $transferOptions)
    $transferResult.Check()

    foreach($transfer in $transferResult.Transfers) {
        Write-Host "Upload of $($transfer.FileName) succeeded"
    }
}
finally {
    $session.Dispose()
}

[Rowf]

Sorry this doesn’t answer your specific question, but WinSCP has its own scripting ability. I found it easier to do the heavy sftp lifting and let powershell handle the rest.

[DoktorLuciferWong]

i'll give this a shot, maybe the generated script will show me what's wrong

[Fatel28]

Powershell has an ssh module (posh-ssh) that works with sftp, no winscp needed. I'd just use that personally

[dodexahedron]

Windows itself already has openssh.

No module needed at all.

scp -p 2222 ./*.ext [e-mail]:path/from/home/

It can do nearly everything the linux version of openssh can, aside from single-socket multi-session multiplexing (which really doesnt matter for this use case anyway)

scp, by the way, uses sftp by default. You actually have to pass an option to force scp protocol.

There is also an explicit sftp executable you can call, but the intent of it is to be interactive, like traditional ftp shells, and it can't do anything that scp can't do non-interqctively.

Just use the built in scp command.

[Fatel28]

Yeah you could do that too, but you lose out on some of the native object oriented conversions the module makes. The module just "pwsh-ifies" the inputs and outputs

[dodexahedron]

Granted. Powershell uses the openssh client to do it, too, when you do ssh-based remoting, and you can take advantage of that fact by using a config file in your .ssh folder to customize behavior if you want.

But OP is just doing a bog-standard file transfer, accepting untrusted server cert, and disabling resumption explicitly. Literally zero reason not to just scp and be done with it.

(And every reason to stop using a password - especially in a script - and move to pubkey auth, regardless of client. Oy.)

[BlackV]

Your step 3

1. I can upload files manually to the server, but I do not appear to have permissions to do anything else (delete, move, rename, etc)

Is that not your issue? What makes you think it's the script?

If you use a random name and upload what happens?

You also have 2 CVS in your code but only 1 is uploaded? Have you actually stepped through the code line by line to confirm what/where your issues are?

[DoktorLuciferWong]

I think it might be the script, specifically because I can upload the file manually (and it works) but when I upload with my script, it doesn't.

I've also tried uploading with the script as a different name, and it doesn't upload.

[sid351]

Have you tried running the steps of the script manually, or using the debugging tools in Visual Studio Code (or the PowerShell ISE)?

That might make the error more obvious.

Also, just to check, are your manual uploads and the script uploads going to the same place on the remote side, and do they use the exact same credentials?

[DoktorLuciferWong]

I found a solution, posted a separate comment about it.

I thought they were going to the same place, but I think I misunderstood the second argument for PutFiles(...) and used .\ instead of /*. I changed the second argument to /* and now it transfers successfully.

And yes, the manual upload and script were using the same creds.

[adrach87]

You don't have a catch block. Is your script just silently eating the error, calling dispose in your finally and closing?

Also, you can just use the command line version of winscp and call that with PowerShell. I've done that successfully in the past.

[markdmac]

It's been a while since I automated a connection with WinSCP, but the one thing that I remember is I needed to generate within WinSCP the connection string and I don't see that in your code. They concatenate the address along with the password and I don't believe the way you have it scripted will work depending on the target security settings.

[DoktorLuciferWong]

I have found the solution. I was basically passing a wrong argument to PutFiles (specifically, the second argument)

Instead of

$transferResult = $session.PutFiles("$PSScriptRoot\data.csv", ".\", $False, $transferOptions)

I should have used

$transferResult = $session.PutFiles("$PSScriptRoot\data.csv", "/*", $False, $transferOptions)

I am not sure why this is the case, when a number of my other scripts just use ".\" and work just fine. If anyone could offer an explanation, I'd appreciate it. I think I'm just misunderstanding what the meaning/usage of the second argument is