Error: Unsupported SMO DataType: numeric

[AndreKonijnendijk]

Hello,

I'm trying to import a csv file downloaded from an AS400.
But I keep getting an error "Unsupported SMO DataType: numeric".

Processing only alphanumeric fields is working fine, but when I try to import a numeric field, this error is thrown.

The strange thing is, the same script is working on one machine, but not on my local machine!!!!

Can you help me with solving this problem?

The Importfile is attached
Testimport.csv

The code for processing the import:
$index = 0
$Columnmap = New-Object System.Collections.Hashtable

$colNames = $Header.Split($NewDelimiter)
foreach ($colname in $colNames) {
    $Columnmap[$index] = $colname
    $index++
}

Import-FktDbaCsv -Parameters @{ Path = $FileInfo.FilePath ; 
    Database = $DatabaseName; 
    Schema = $SchemaName; 
    Table = $TableName; 
    TrimmingOption = "None"; 
    Delimiter = $NewDelimiter; 
    ColumnMap  = $Columnmap;
} 

Function Import-FktDbaCsv {
[cmdletbinding()]
Param
(
[Parameter(Mandatory = $true)]
[hashtable]$Parameters
)
# With splatting
# Note SqlBulkCopy is case sensitive e.g. with column names
# ENSURE all values for source column name are valid and case sensitive.
# ENSURE all values for destination column name are valid and case sensitive.
# MAKE the source case insensitive
Import-DbaCsv -SqlCredential (Get-FktUSoftCredential) -SqlInstance (Get-ServerName) -EnableException -NoHeaderRow -NoProgress @parameters
}

The Error I'm getting: error.txt
The columnmapping:
Colomnmap.log
Definition of the table:
table.txt

[niphlod]

can you run on the latest dbatools version with -Verbose ?

[AndreKonijnendijk]

I found a difference in used version on both machines. I have uninstalled dbatools and installed it with the same version, installed on the working machine. It is now working wel.

I will give it a try with -Verbose parameter when I am done with my task

[AndreKonijnendijk]

Installed the latest version of DBAtools (2.1.26) and dbatools.library (2024.4.12)
Added the -Verbose parameter. Same Error

[niphlod]

okay, care to paste the -verbose log ?

[AndreKonijnendijk]

VERBOSE: [13:13:09][Import-DbaCsv] Started at 10/23/2024 13:13:09
VERBOSE: [13:13:09][Connect-DbaInstance] Starting loop for 'xx\xx: ComputerName = 'xx', InstanceName = 'xx', IsLocalHost = 'True', Type = 'Default'
VERBOSE: [13:13:09][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
VERBOSE: [13:13:09][Connect-DbaInstance] authentication method is 'local sql'
VERBOSE: Performing the operation "Starting transaction in fkt" on target "xxx\xxx".
VERBOSE: [13:13:09][Import-DbaCsv] Table exists
VERBOSE: [13:13:09][Import-DbaCsv] Starting bulk copy for 4_CALEN_CA_LEVERANCIER_csv.csv_
VERBOSE: Performing the operation "Performing import from C:\FKT_AS_US\in\4_CA_LEVERANCIER\4_CALEN_CA_LEVERANCIER_csv.csv_" on target "xxx\xxxInterGamma".
VERBOSE: [13:13:09][Import-DbaCsv] ColumnMap was supplied. Additional auto-mapping will not be attempted.
Unsupported SMO DataType: numeric
At line:98163 char:9

•     throw $records[0]
  

•     ~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (:) [], Exception
  • FullyQualifiedErrorId : dbatools_Import-DbaCsv