sql - How do I get the last file from a hot folder using PowerShell? -

this question has answer here:

• count items in folder powershell 5 answers

we have client uploads 1 .pkg file each hour specified folder on our ftp. want create sql server agent job grab file, import data table in sql server db, move , rename file.

i successful in doing (using code below) except when there 1 file left. when there 1 file left not import... , moves actual folder file in (renaming folder this). have provided errors below.

script:

function autoimportcommaflatfilestopone($location, $server, $database) {     $connection = new-object system.data.sqlclient.sqlconnection     $connection.connectionstring = "data source=" + $server + ";database=" + $database + ";integrated security=true"       $files = get-childitem $location      $filename = $files[0]       $full = $location + $filename     $table = "rawusps"       $insertdata = new-object system.data.sqlclient.sqlcommand     $insertdata.commandtext = "execute stp_commabulkinsert @1,@2"     $insertdata.parameters.add("@1", $full)     $insertdata.parameters.add("@2", $table)     $insertdata.connection = $connection      $connection.open()     $insertdata.executenonquery()     $connection.close()      move-item $full (("c:\test\archivefolder\{0:yyyymmdd_hhmmss}" + "_" + $filename.basename + ".log") -f (get-date))  }  autoimportcommaflatfilestopone -location "c:\test\testfolder\" -server "lan-dp-03" -database "flatfileinserttestingdb" 

the errors:

ps c:\users\rcurry> c:\scripts\140627.ps1 unable index object of type system.io.fileinfo. @ c:\scripts\140627.ps1:8 char:24 +     $filename = $files[ <<<< 0] + categoryinfo          : invalidoperation: (0:int32) [], runtimeexception + fullyqualifiederrorid : cannotindex    exception calling "executenonquery" "0" argument(s): "cannot bulk load because file "c:\test\testfolder\" not opened. operating system error code 3(the system cannot find path specified.)." @ c:\scripts\140627.ps1:21 char:32 +     $insertdata.executenonquery <<<< () + categoryinfo          : notspecified: (:) [], methodinvocationexception + fullyqualifiederrorid : dotnetmethodexception 

i figured out problem. per hydropowerdeveloper @ stackoverflow.com/questions/14714284/… : > well, turns out quirk caused precisely because there 1 file in directory. searching revealed in case, powershell returns scalar object instead of array. object doesn’t have count property, there isn’t retrieve. needed force array using '@'.