import-dbacsv locks csv file on Error

[ghost]

Hi

I am trying to use the import-dbacsv function to import a bunch of files in directory structure. It works well so far except for one issue.

Im finding that a csv file will become locked by powershell if there is an error thrown by import-dbacsv. I need to handle a file that has not been processed by moving it from is current filepath to a new location. An example of an error i want to catch is if a primary key constraint has been violated .

Here is a example of what happens:

This is error picked up by import-dbacsv when duplicate data is imported :
Import-DbaCsv -Path "$filetoprocess" -SqlInstance $sqlinstance -Database $databasename -Table $destinationtable -Delimiter "," -EnableException

WARNING: Error message: Violation of PRIMARY KEY constraint 'PK_Gas_Composition_Data'. Cannot insert duplicate key in object 'dbo.Ga
s_Composition_Data'. The duplicate key value is (2001-06-21, Somewhere Else).
The statement has been terminated.

//////////////////////////////////////////////////////////////////////////////////////////////////////////
I capture the file name that i need to move but powershell keeps a lock on the csv file. It's like import-dbacsv command does not release the file lock on a file that i can not import ?

/////////////////////////////////////////

D:\FlatFileIImports\Gas_Composition_Data\Gas_Composition_Data1 - Copy.csv
VERBOSE: Performing the operation "Move File" on target "Item: D:\FlatFileIImports\Gas_Composition_Data\Gas_Composition_Data1 - Copy
.csv Destination: D:\FlatFileArchive\BadFiles\Gas_Composition_Data1 - Copy.csv".
Move-Item : The process cannot access the file because it is being used by another process.
At C:\Scripts\SCENE-ImportData-TEST.ps1:114 char:1

• Move-Item $file -Destination $badfilepath -Force -Verbose

•   + CategoryInfo          : WriteError: (D:\FlatFileIImp...ata1 - Copy.csv:FileInfo) [Move-Item], IOException
    + FullyQualifiedErrorId : MoveFileInfoItemIOError,Microsoft.PowerShell.Commands.MoveItemCommand
  
  
  

`#Variables MAKE CHANGES HERE#################################
$servername = "ausappd01"
$databasename = "SCENE"
$importpath = "D:\FlatFileIImports"
$sqlinstance = "ausappd01\MSSQLSERVER"
$directory = Get-ChildItem D:\FlatFileIImports | select Name
$flatfilearchive = "D:\FlatFileArchive"
$badfilepath = "D:\FlatFileArchive\BadFiles"
$badfiles = New-Object System.Collections.Generic.List[System.Object]
$processedfiles = New-Object System.Collections.Generic.List[System.Object]
##################################################################
$a = Get-DbaUptime -SqlInstance $sqlinstance

#Function: Get-TableNames
#Purpose: Query Database for Tables
Function Get-TableNames(){
$srv = new-object microsoft.sqlserver.management.smo.server '.';
foreach ($db in $srv.Databases) {
foreach ($tbl in $db.tables) {
if ($db.Tables.Parent -like "$databasename"){
$a = $db.Tables.Parent.Tables
return $a
#$tbl | select parent, name;
}
}
}
}#End Function

#Get Tables out of the database
$tables = Get-TableNames

#Loop the SQL tables and dir structure and match for import
foreach ($table in $tables){
$destinationtable = $table.Name
if ($directory -match $destinationtable){

    write-host "Found Matching Table " $destinationtable `n

    #Check and Create Archive Dir
    if(![System.IO.File]::Exists("D:\FlatFileArchive\$destinationtable")){
        # file with path $path doesn't exist
        New-Item -Path D:\FlatFileArchive\$destinationtable -ItemType Directory -ErrorAction Continue
    }else{
        write-host "`n Archive Already Directory Exists `n"
    }

    Write-host "**********Begin Import SQL IMPORT for Table dbo.$destinationtable ******** `n" 
    $filepath= $importpath + "\" + $destinationtable
    write-host "File Import Path" $filepath
    $csv = Get-ChildItem $filepath | select Name
    if ($csv -eq $null){
    write-host "NO Files Found to Process Write to Log `n"
    }else{

    foreach ($csvfile in $csv){
     $errorlvl = $null
     $filetoprocess = $filepath + "\" +$csvfile.Name
     $filearchive = $flatfilearchive + "\" + $destinationtable

     Write-Host "`n SQL IMPORT Process File" $filetoprocess
    
      #SQL IMPORT
       ## Import CSV into SQL
      try{
 
      Import-DbaCsv -Path "$filetoprocess" -SqlInstance $sqlinstance  -Database $databasename -Table $destinationtable -Delimiter "," -EnableException
      
      }
      catch{
      $errorlvl = $_
      Write-Warning "Error message: $_"
      write-host "Collect Bad Files to Process"
      $badfiles.Add("$filetoprocess")


      }
      
      if ($errorlvl -eq $null){
      #Archive the file
      Move-Item -Path $filetoprocess -Destination $filearchive -Force -Verbose
      $processedfiles.Add($filetoprocess)
      }
     }#Endfor

    }#Endelse
    }#Endif

}#endfor

function Process-BadFiles(){

foreach ($file in $badfiles){

Move-Item $file -Destination $badfilepath -Force -Verbose

}#endfor

}#endfuction

Write-host "n These are the processed Files" $processedfiles Write-host "n These are the bad Files"
$badfiles
Process-BadFiles`