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

Question

We have a client that uploads one .pkg file each hour to a specified folder on our FTP. I want to create a SQL Server Agent Job to grab that file, import the data into a table in a SQL Server DB, then move and rename the file.

I am successful in doing this (using the code below) except when there is only 1 file left. When there is only one file left it will not import... and then it moves the actual folder with the file in it (renaming the folder as it does this). I have also provided the 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 to index into an object of type System.IO.FileInfo.
At C:\Scripts\140627.ps1:8 char:24
+     $fileName = $files[ <<<< 0]
+ CategoryInfo          : InvalidOperation: (0:Int32) [], RuntimeException
+ FullyQualifiedErrorId : CannotIndex



Exception calling "ExecuteNonQuery" with "0" argument(s): "Cannot bulk load because the file "C:\Test\testFolder\" could not be opened. Operating system error
code 3(The system cannot find the path specified.)."
At C:\Scripts\140627.ps1:21 char:32
+     $insertData.ExecuteNonQuery <<<< ()
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

I figured out the problem. Per hydropowerdeveloper at http://stackoverflow.com/questions/14714284/count-items-in-a-folder-with-powershell : > Well, it turns out that this is a quirk caused precisely because there was only one file in the directory. Some searching revealed that in this case, PowerShell returns a scalar object instead of an array. This object doesn’t have a count property, so there isn’t anything to retrieve. So I simply needed to force an array using '@'. — rcurrydev, Jun 27 '14 at 14:52
If you discovered the solution yourself, please post it as an answer of your own and accept it, so that your question is removed from the unanswered questions queue. — Ansgar Wiechers, Jun 27 '14 at 15:05

score 0 · Accepted Answer · answered Jul 07 '14 at 19:34

I figured out the problem. Per hydropowerdeveloper at stackoverflow.com/questions/14714284/… : > Well, it turns out that this is a quirk caused precisely because there was only one file in the directory. Some searching revealed that in this case, PowerShell returns a scalar object instead of an array. This object doesn’t have a count property, so there isn’t anything to retrieve. So I simply needed to force an array using '@'.

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

1 Answers1