Powershell script transfers entire directory?

  • I've got a powershell script I'm using to send one file via SFTP. The only problem? It's copying everything in the directory instead of just the one file. It's got to be something in my variable set up, but I can't see the problem. Can someone please take a look at this and tell me why it's ignoring my Where-Object clause?

    $remotePath = "/MyServer/landing_zone/Folder1/" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})

    $localPath = "\\Mynas\Folder1\Folder2\" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})

    I need the wild card because the file is dated with each day's date. But for some reason, the code seems to assume all files should be copied. Help!

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Are those two lines in the same order as they are in your script?
    If so, the first problem I can see is that the $remotepath isn't going to populate correctly (I suspect.)
    That might cause what you're seeing, as when it tries to get the list of files from $localpath, it fails, so it's just going to grab everything.

    Any chance you can post everything involved in the copy, in the exact order it is in the script?

  • jasona.work - Monday, July 24, 2017 7:26 AM

    Are those two lines in the same order as they are in your script?
    If so, the first problem I can see is that the $remotepath isn't going to populate correctly (I suspect.)
    That might cause what you're seeing, as when it tries to get the list of files from $localpath, it fails, so it's just going to grab everything.

    AHHHH! Seriously?

    Why did I not see that? @DOH. Yes, that's the exact order. Before posting the entire script, I'm going to try turning that around a little (and not populate $localpath with the file name until after the $remotepath populates) to see if that works...

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • And that was it... Truly, changing that up a little fixed the problem. Though I don't understand how the $remotepath knew what file directory to start copying to begin with.

    Here's what fixed it:


    $localPath = "\\Mynas\Folder1\Folder2\"
    $remotePath
    = "/MyServer/landing_zone/Folder1/" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})

    $localPath = "\\Mynas\Folder1\Folder2\" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})

    Not clean, but it works.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • It knew the start directory because it's in your $remotepath:
    $remotePath = "/MyServer/landing_zone/Folder1/"
    Just for giggles I took your $remotepath and set it up on my PC, pointing to a valid server share, with a bad where-object like.  When I checked the value of $remoteobject, it returned the share path.

  • jasona.work - Monday, July 24, 2017 9:04 AM

    It knew the start directory because it's in your $remotepath:
    $remotePath = "/MyServer/landing_zone/Folder1/"
    Just for giggles I took your $remotepath and set it up on my PC, pointing to a valid server share, with a bad where-object like.  When I checked the value of $remoteobject, it returned the share path.

    The remote path is on a different server in a different location than the local path. Which is why it confuses me how it knew to copy local path contents to the remote path.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Monday, July 24, 2017 9:27 AM

    The remote path is on a different server in a different location than the local path. Which is why it confuses me how it knew to copy local path contents to the remote path.

    Wait, which way was the copy going?
    From $localpath -> $remotepath or the other way 'round?

  • jasona.work - Monday, July 24, 2017 10:11 AM

    Brandie Tarvin - Monday, July 24, 2017 9:27 AM

    The remote path is on a different server in a different location than the local path. Which is why it confuses me how it knew to copy local path contents to the remote path.

    Wait, which way was the copy going?
    From $localpath -> $remotepath or the other way 'round?

    Local path to remote.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Monday, July 24, 2017 10:17 AM

    jasona.work - Monday, July 24, 2017 10:11 AM

    Brandie Tarvin - Monday, July 24, 2017 9:27 AM

    The remote path is on a different server in a different location than the local path. Which is why it confuses me how it knew to copy local path contents to the remote path.

    Wait, which way was the copy going?
    From $localpath -> $remotepath or the other way 'round?

    Local path to remote.

    OK, now it's moved into "I'd need to set up something similar and test it" because I would think it would only grab the appropriate file in that case.

  • Heh... I'll likely not learn much more about PoSh than I already know.  For things like this, I build a good ol' fashion DOS command in T-SQL and use xp_CmdShell to execute it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jasona.work - Monday, July 24, 2017 10:28 AM

    OK, now it's moved into "I'd need to set up something similar and test it" because I would think it would only grab the appropriate file in that case.

    Here's the code with the fix commented out.

    $erroractionpreference = "Stop"

     # Load WinSCP .NET assembly
     [Reflection.Assembly]::LoadFrom("\\ServerName\D$\Program Files\WinSCP\WinSCPnet.dll")

     # Setup session options
     $sessionOptions = New-Object WinSCP.SessionOptions
     $sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
     $sessionOptions.HostName = "RemoteSite"
     $sessionOptions.UserName = "Login"
     $sessionOptions.Password = "password"
        $sessionOptions.GiveUpSecurityAndAcceptAnySshHostKey = "true"

        #$localPath = "\\Mynas\Folder1\Folder2\"
        $remotePath = "/MyServer/landing_zone/Folder1/" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})
     $localPath = "\\Mynas\Folder1\Folder2\" + (Get-ChildItem $localPath | Where-Object {$_.Name -like "File_Daily_ABC.123.XYZ.DLY_*"})

        #$wshell = New-Object -ComObject Wscript.Shell
        #$wshell.Popup("The path is " + $localPath + " or is perhaps " + $remotePath )

     # Create Session
     $session = New-Object WinSCP.Session

     # Connect
     $session.Open($sessionOptions)

     # Upload files
     $transferOptions = New-Object WinSCP.TransferOptions
     $transferOptions.TransferMode = [WinSCP.TransferMode]::Ascii
        $transferOptions.PreserveTimestamp = $FALSE

     $transferResult = $session.PutFiles($localPath, $remotePath, $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 \\Mynas\Folder1\Folder2\SFTP_Daily_Log.txt -Append
      }

     # Disconnect, clean up
     if (!($session))
      {
      $session.Dispose()
      }

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeff Moden - Monday, July 24, 2017 7:23 PM

    Heh... I'll likely not learn much more about PoSh than I already know.  For things like this, I build a good ol' fashion DOS command in T-SQL and use xp_CmdShell to execute it.

    Corporate is trying to kibosh use of xp_CmdShell in any environment. We're having to fight for an exception because we only use it for DBA stuff, but they're worried about security problems. So we might lose that fight.

    Plus, in this instance, we MUST use SFTP. Even though it's going to another department in-house. So unless you know a good way to use SFTP via xp_CmdShell (please tell me if you do), PoSh is Da Thing.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Thursday, July 27, 2017 5:33 AM

    Jeff Moden - Monday, July 24, 2017 7:23 PM

    Heh... I'll likely not learn much more about PoSh than I already know.  For things like this, I build a good ol' fashion DOS command in T-SQL and use xp_CmdShell to execute it.

    Corporate is trying to kibosh use of xp_CmdShell in any environment. We're having to fight for an exception because we only use it for DBA stuff, but they're worried about security problems. So we might lose that fight.

    Plus, in this instance, we MUST use SFTP. Even though it's going to another department in-house. So unless you know a good way to use SFTP via xp_CmdShell (please tell me if you do), PoSh is Da Thing.

    Heh... that did come out loud, didn't it?  I was thinking back to how I used to do it.  Sounds like you've got it sussed with PoSh and, considering the coming corporate edict on not using xp_CmdShell, it would likely be a waste of both our time to write an updated example.

    As for a DOS/CLI method to do it, I used to (on-the-fly ) build the necessary batch files (1 to get the directory listing using wildcards to produce it and 1 to download the files using separate GET commands created from the output of the first) from SQL Server according to connection tables for each site I had to download from.  It was easier (at the time and long before PoSh was a gleam in someone's eye) to create the necessary batch files and execute them than trying to do it all from DOS/CLI (CLI = Command Line Interface = DOS Prompt, just in case someone reading this doesn't know).  I also did it using SQL Server so that I could take advantage of SQL Agent for scheduling and logging, not to mention the fact that the files were ultimately loaded into tables in SQL Server.  The files contained CDRs (Call Detail Records) downloaded from PollCat telephone buffers for multiple sites for each of multiple companies.  The really cool part was that I didn't have to write any bloody loops, which I hated :sick: doing way back in my ol' VB days.  Damn....  That came out loud, too, didn't it? 😀

    For those that might not know, here's a resource that explains how to do such things manually from the DOS/CLI.  No matter the method you choose to automate the process, it's a pretty good tutorial of the more useful SFTP commands themselves.

    http://www.jscape.com/blog/using-sftp-on-command-line

    As for the fear that drives people away from things like xp_CmdShell and OLE Automation, most people are responding to fear rather than true understanding (especially at the corporate level because they just don't know) .  Doing things like disabling xp_CmdShell doesn't buy you anything security wise especially if (and ironically) your server isn't secure and it deprives DBAs and extreme development of some great and easy to use tools.  Here's a presentation I did several years back on the subject, which also explains how to use it securely.

    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Saturday, July 29, 2017 10:24 AM

    As for the fear that drives people away from things like xp_CmdShell and OLE Automation, most people are responding to fear rather than true understanding (especially at the corporate level because they just don't know) .  Doing things like disabling xp_CmdShell doesn't buy you anything security wise especially if (and ironically) your server isn't secure and it deprives DBAs and extreme development of some great and easy to use tools.  Here's a presentation I did several years back on the subject, which also explains how to use it securely.

    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    I was hoping you'd post this somewhere. Thanks for the link. It might help us fight for the right to use CmdShell.

    (xp_CmdShell doesn't quite fit the rhythm of the line I was going for @=).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply