Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Verify File Exists Using PowerShell


By:   |   Last Updated: 2019-02-19   |   Comments (2)   |   Related Tips: More > PowerShell

Problem

For migrating or creating our ETL and application libraries, we maintain an expected file list in a file that we use to compare to the actual library files migrated, against our expected file list. We've found that sometimes our libraries experience errors during creation or migration, such as a file failing to copy or a non-required file not generating during creation, resulting in a missing file (or more). We do this comparison between what should exist and what does exist manually, is there a way to use PowerShell to automatically compare a list of files we expect to find to what actually exists, so that we can create automatic alerts around this script?

Solution

In this tip, we'll look at one way in which we can achieve this using a built in PowerShell function by comparing two ETL libraries, one of which was migrated (or created) correctly and the other which was not. We will first look at how to do this manually and after that, we'll look at how to wrap this function for re-use and with a return output that we may want to use for alerting.

Example Comparison of Files

For the sake of our example, we'll assume that our ETL library should have 6 files: data.config, datarestrict.dll, dictionary.config, runner.dll, readme.txt, and migrate.ps1. We'll have an expected file list that we'll use which has each file that should exist in the ETL library named etlValidate_expected.txt.

In the below code snippet, we'll return the comparison between the expected file list for the ETL library defining what we should see and what we actually see present in the library after the migration (in this case, we'll assume a migration, but this could be used in other contexts - like creation).

Our first line reads the contents of the expected file list using Get-Content and we'll notice its location relative to the where the files should actually be migrated to (two directories up): if we're using this format with a file that holds an "expected" design in a live environment, we may want to avoid storing this where the application or library files are for security reasons. An alternative would be to remove it once we've validated what we need and it's worth noting this design is intended for security.

We then get the name of the files from the Name property from Get-ChildItem. We then use PowerShell's Compare-Object function to compare the actual files ($migratedfiles) to the expected files ($expectedfilelist) intentionally so that we compare what exists to what should exist and a right arrow (=>) will indicate it's only in the expected file list. We could use a different order, using the expected files first and the actual files second, provided that we recognize what the output will mean (a left arrow of <=).

Our output shows that in the case of our validateRunner2 library, we have all the files since the output shows that everything is equal (==):

$expectedfilelist = Get-Content "C:\sites\etlValidate_expected.txt"
$migratedfiles = (Get-ChildItem "C:\sites\etlValidate\validateRunner2\").Name
Compare-Object $migratedfiles $expectedfilelist -IncludeEqual
input object

For another example, ETL library validateRunner1 has a file missing. When we compare the above successful result with all values being equal with the validateRunner1 library that does not have all the files present, we see the difference in the result - the readme.txt file is not present in the actual ETL library in the path C:\sites\etlValidate\validateRunner1\:

$expectedfilelist = Get-Content "C:\sites\etlValidate_expected.txt"
$migratedfiles = (Get-ChildItem "C:\sites\etlValidate\validateRunner1\").Name
Compare-Object $migratedfiles $expectedfilelist -IncludeEqual
side indicator

As a development note, our variable $migratedfiles returns multiple objects (in this case, string names of files), so we'll consider this return when we create our wrapper function for re-use. In addition, we move two directories higher to read the expected file list. It's possible that the list could be up three directories or none. While two higher directories is often a default in some contexts, we'll want our wrapper function to allow the default without requiring a parameter, or an optional parameter that can specify any amount higher (or on the same level). We'll change directory (cd) from where the migrated files should be to where our expected file list is. Just in case this is higher up than 2 or even lower than 2, as we see in the below code snippet from the PowerShell command line, we can multiply a string by using multiplication:

Write-Host ("..\" * 0)
Write-Host ("..\" * 5)
write host

PowerShell Function to Find Missing Files that are Expected

We now add our wrapper function together that only requires the file list name and the directory of where the migrated (or created) files should be.

We are assuming that the file list is a text file; if the extension can change, we would remove the .txt in the line $filelist = Get-Content "$multiple$expectedfilelist.txt".

Function Verify-Files {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true)][string]$expectedfilelist
        , [Parameter(Mandatory=$true)][string]$migratedfiles
        , [Parameter(Mandatory=$false)][int]$higherdirectory
    )
    Process
    {
        [string[]]$migrated = (Get-ChildItem $migratedfiles).Name

        cd $migratedfiles
        if ($higherdirectory -ne "") 
        { 
            $multiple = ("..\" * $higherdirectory)
            $filelist = Get-Content "$multiple$expectedfilelist.txt"
        }
        else
        {
            $filelist = Get-Content "..\..\$expectedfilelist.txt"
        }

        Compare-Object $migrated $filelist -IncludeEqual
    }
}

Verify-Files -expectedfilelist "etlValidate_expected" -migratedfiles "C:\sites\etlValidate\validateRunner2\"
input object

As a final note, if we want to only return the results that don't match, we can remove the -IncludeEqual parameter, calling the final line of Compare-Object $migrated $filelist instead of the line with the parameter.

Next Steps
  • We're returning everything that is equal or not. We could filter on the SideIndicator being =>, which shows that the expected file list has a file that the migration path does not. This may be useful, provided we know the expected file list will never have less than what is in the migrated path. A safer alternative to avoid this assumption would be to remove the -IncludeEqual parameter.
  • One underlying assumption here per the question is that the expected file list is maintained. If it's not, we may see a SideIndicator of <=, highlighting that our expected file list doesn't have a file that we find in the migration (or creation) path.
  • The above script is using PowerShell version 5. PowerShell's included functions can sometimes change, relative to the version. You can verify your version of PowerShell by getting the PSVersion property, calling $PSVersionTable.PSVersion and looking at the major value.


Last Updated: 2019-02-19


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 28, 2019 - 4:27:49 PM - Tim Back To Top

@Robert

Great question Robert.  I tested the most recent release listed on PowerShell's GitHub and it functioned.  Given the version I downloaded is command line, if calling from a file, the reference pointers will be key.  I was surprised that I could just copy and paste the function without issue.


Thursday, February 28, 2019 - 3:28:15 AM - Robert Sterbal Back To Top

What versions of PowerShell does this work with other than version 5? Does it work with PowerShell Core? --Robert Sterbal


Learn more about SQL Server tools