The given path's format is not supported (but it looks ok to me)

Question

I've tried a million combinations to fix and create the directory I need, and it works when I run the powershell IDE, and when I run the task that's scheduled, but it fails overnight in the scheduled run.

Right now, this is what I'm doing, which is extremely redundant, to try and catch the issue. I can't tell where it's failing specifically in my script, since it doesn't give a line number, but I think it's the initial dir creation (see #create sub-folder location). All I can think is that this is the first time the script is run in the sequence, and it hasn't finished creating the dated parent dir when it gets to the subdir. The parent dir is fine format-wise as well.

Error message:

"Alert: Disaster Backup failed on workstation DT2. Error Message: Error in Privilege script. Length error The given path's format is not supported.The given path's format is not supported.The given path's format is not supported.Exception: System.Management.Automation.ItemNotFoundException: Cannot find path 'E:\DisasterBackup\toLoc_2019-01-30\Privileges\Privileges_1_Bak.csv' because it does not exist.
   at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(String path, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletProvider provider, CmdletProviderContext context)
   at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPath(String path, CmdletProviderContext context, Boolean allowNonexistingPaths, CmdletProvider& providerInstance)
   at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFromMonadPath(String path, Boolean allowNonexistingPaths, CmdletProviderContext context, CmdletProvider& providerInstance)
   at System.Management.Automation.LocationGlobber.GetGlobbedProviderPathsFromMonadPath(String path, Boolean allowNonexistingPaths, CmdletProviderContext context, ProviderInfo& provider, CmdletProvider& providerInstance)
"

Script:

function SQLQueryWriteToFile([string]$SQLquery, [string]$extractFile, [string]$facility)
{
   #one last check that the dir exists for destination
   $to_loc_final = Split-Path $extractFile
   if(-Not (Test-Path $to_loc_final )) 
   {
      write-output  " Creating folder $to_loc_final because it does not exist " 
      $global:ErrorStrings.Add("Creating folder $to_loc_final fourth time ")
      New-Item -ItemType directory -Path $to_loc_final -force 
      if(-Not (Test-Path $to_loc_final )) ##############
      {
         write-output  " Creating folder $to_loc_final because it does not exist second " 
         $global:ErrorStrings.Add("Creating folder $to_loc_final fifth time ")
         New-Item -ItemType directory -Path $to_loc_final -force 
         if(-Not (Test-Path $to_loc_final )) 
         {
            write-output  " Creating folder $to_loc_final because it does not exist third " 
            $global:ErrorStrings.Add("Creating folder $to_loc_final sixth time ")
            New-Item -ItemType directory -Path $to_loc_final -force 
         }
      }
   }
  [System.Data.SqlClient.SqlConnection] $sqlConnection=$null
  [System.Data.SqlClient.SqlCommand] $sqlCmd=$null
  try
  {

   $sqlConnection = New-Object System.Data.SqlClient.SqlConnection


   $sqlConnection.ConnectionString = "Server=sqlm;Database=DB;User ID=user;Password=pw"  #db
   $sqlConnection.Open()

   #Create a SqlCommand object to define the query
   $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
   $sqlCmd.Connection = $sqlConnection
   $sqlCmd.CommandText = $SQLquery
   $sqlCmd.Parameters.Add('@facility',$facility)

   if($sqlConnection.State -ne 'Open'){
         $global:ErrorStrings.Add("Exception: $("Couldn't connect to DB with connection string given");;  ")
   } #if
   elseif($sqlConnection.State -eq 'Open'){
      #create a SqlAdapter that actually does the work and manages everything
      $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $sqlAdapter.SelectCommand = $sqlCmd
      $sqlAdapter.SelectCommand.CommandTimeout=300  #set timeout for query execution to 5 minutes (60x5=300)

      #create an empty dataSet for the query to fill with its results
      $dataSet = New-Object System.Data.DataSet

      #execute the query and fill the dataSet (then disconnect)
      $sqlAdapter.Fill($dataSet)
      $sqlConnection.Close()


      #dump the data to csv
      $DataSet.Tables[0] | Export-Csv $extractFile ##this is where the path error is first thrown
      if($DataSet.Tables[0].Rows.Count -eq 0)
      {
         $result = ($SQLquery -split '[\n]')[0] #first line of query gives enough identifying info
         $global:ErrorStrings.Add("Exception: $("DataSet returned empty results for query $result")  ")
      } #if
   } #elseif


  }#try
  catch
  { ##I found out this is where the error is caught about path in question title
     $tempMsg = "caught an error in SQL Query method:" + $_.Exception.Message
     $global:ErrorStrings.Add($tempMsg)
  }
  finally
  {

      if($sqlConnection -ne $null)
      {
         $sqlConnection.Close()
         $sqlConnection.Dispose()
      }


  } #finally


} 


#create dated folder to put backup files in 
function CreateDatedFolder([string]$name){
   $datedDir = ""
   $datedDir = "$name" + "_" + "$((Get-Date).ToString('yyyy-MM-dd'))"
   New-Item -ItemType Directory -Path $datedDir
   return $datedDir
}

################################## start here #########################################################################
$SQLquery_Privilege = @"
SELECT *

 FROM "TABLEName

WHERE 
       Status in ('Active')  
          and Facility = @facility
          and Last not like ('%test%')
          and Last not like ('%Test%')

--ORDER BY Last
"@



$parentDirBaseName = "E:\DisasterBackup\toLoc" 
$toLocParent = CreateDatedFolder $parentDirBaseName 
$to_loc_final = Join-Path -Path $toLocParent -ChildPath "Privileges" 
#create sub-folder location 
if(-Not (Test-Path $to_loc_final )) 
{
   write-output  " Creating folder $to_loc_final because it does not exist " 
   $global:ErrorStrings.Add("Creating folder $to_loc_final first time ")
   New-Item -ItemType directory -Path $to_loc_final -force 
   if(-Not (Test-Path $to_loc_final )) 
   {
      write-output  " Creating folder $to_loc_final because it does not exist second " 
      $global:ErrorStrings.Add("Creating folder $to_loc_final second time ")
      New-Item -ItemType directory -Path $to_loc_final -force 
      if(-Not (Test-Path $to_loc_final )) 
      {
         write-output  " Creating folder $to_loc_final because it does not exist third " 
         $global:ErrorStrings.Add("Creating folder $to_loc_final third time ")
         New-Item -ItemType directory -Path $to_loc_final -force 
      }
   }
}

$global:IT = "\\DRIVE\IT\DisasterRecovery\Privileges\"
$global:ErrorStrings = New-Object System.Collections.Generic.List[System.String]

$extractFiles = @("Privileges_0_Bak.csv","Privileges_1_Bak.csv","Privileges_2_Bak.csv")
$facCode = @("H0","H1","H2")

#Loop through list of files and facilities for Practitioner Privileges
for($i=0; $i -lt ($facCode.Length); $i++) {
   $tempToLoc = Join-Path -Path $to_loc_final -ChildPath $extractFiles[$i]
   SQLQueryWriteToFile $SQLquery_Privilege $tempToLoc $facCode[$i]
}

[string] $errorCodeAsString = ""
foreach ($item in $global:ErrorStrings){
   $errorCodeAsString += $item 
}
if($errorCodeAsString -ne "")
{
   $errorCodeAsString = [string]$errorCodeAsString
   $errorCodeAsString = "Error in Privilege script. Length error $errorCodeAsString.length " + $errorCodeAsString
   $ScriptPath = Split-Path $MyInvocation.InvocationName
   $ScriptPathFilename = Join-Path -Path $ScriptPath -Child "\EmailAlertFailure.ps1"
   & $ScriptPathFilename $errorCodeAsString
   Exit 99
}
Exit 0

I've looked at path format not supported but I don't have a : in my dir path other than c: or e:, which is normal.

For example, the toLoc dir last night was:

'E:\DisasterBackup\toLoc_2019-01-30\Privileges

When I look, the E:\DisasterBackup\toLoc_2019-01-30 is there, but Privileges dir is not. This is the only script I have that is failing to create it's subdir in that folder, and it's doing it the same way. Today I copied the code exactly (for dir creation) to make sure it was the same. It runs fine in IDE and run scheduled task, but I guarantee tonight I will have the same error messages for the Privilege backup script. I checked, and the script name is correct in the task manager as well, which I expected since it runs when I run it manually. The scheduled run at night has the error given above, with resulting SQL file write failing because the subdir isn't there. Any ideas?

Update I found out where the path error is thrown and caught. See added ## comments in the SQLQueryWriteToFile function.

Answer 1

I figured it out. For some reason, it wasn't treating the filename correctly. When I looked at it in the debugger, it was showing me 2 filenames with the same path, so I thought it was just a display issue. I think for some reason it was putting two same name filename paths together into an array. To fix it, I did this (see "this fixed it" comments below):

$parentDirBaseName = "E:\DisasterBackup\toLoc" 
$toLocParent = CreateDatedFolder $parentDirBaseName 

#create sub-folder location
$to_loc_final = Join-Path -Path $toLocParent -ChildPath "Privileges"
New-Item -type directory  -path $to_loc_final -force

$global:MSS_IT = "\\DRIVE\IT\DisasterRecovery\Privileges\"
$global:ErrorStrings = New-Object System.Collections.Generic.List[System.String]

$extractFiles = @("Privileges_0_Bak.csv","Privileges_1_Bak.csv","Privileges_2_Bak.csv")
$facCode = @("H1","H2","H3")

#Loop through list of files and facilities for Practitioner Privileges
for($i=0; $i -lt ($facCode.Length); $i++) {
   $tempFile = $extractFiles[$i] ###this fixed it
   $extractFilePath = Join-Path -Path $to_loc_final -childpath $tempFile ###this fixed it
   New-Item -path $extractFilePath -itemType file -force ###this fixed it
   $extractLoc = $extractFilePath[0] ###this fixed it
   SQLQueryWriteToFile $SQLquery_Privilege $extractLoc $facCode[$i] ###this fixed it
}