'Azure SQL DB external table throws the error after adding column at the end of csv

I need help in following-

Old CSV file has 3 columns- id, fname, lname

New CSV file has 4 columns- id, fname, lname, dob

I have u updated the external schema as below-

CREATE EXTERNAL TABLE [dbo].[employee]
(
    [id] [int] NOT NULL,
    [fname] [nvarchar](50) NULL,
    [lname] [nvarchar](50) NULL,
    [dob] [datetime2](7) NULL
    
)
WITH (DATA_SOURCE = [EmpWarehouseStorage],LOCATION = N'Employee/New',FILE_FORMAT = [PipeFileFormat],REJECT_TYPE = VALUE,REJECT_VALUE = 10)

External Data source Query-

CREATE EXTERNAL DATA SOURCE [EmpWarehouseStorage] 
WITH (TYPE = HADOOP, LOCATION = N'abfss://emp@****.dfs.core.windows.net', 
CREDENTIAL = [EmpWarehouseStorageKey])

External File Format -

CREATE EXTERNAL FILE FORMAT [PipeFileFormat] WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', USE_TYPE_DEFAULT = False))

Querying external table throws the error -

1*** rows rejected from external table [Employee] in plan step 2 of query execution: Location: 'Employee/New/Employee_2***.csv' Column ordinal: 79, Expected data type: datetime2.

Just for debugging purposes, I have increased the REJECT_VALUE = 10000. I get to know that external shows only the new files data and does not show old files data.

sqlsql-serverazure-sql-databaseexternal-tables


Solution 1:[1]

Just from the error message, it seams that the column data of dob can not be converted to data type datetime2(7).

We don't know what string your dob data look like, please check it follow this link:Supported string literal formats for datetime2.

So the solution could be that change the datetime2 to other date data type.

Update:

Glad to hear you found the cause:

  • It is not just because of datetime2Actually it is related with any data type. As previous files doesn't have that column it is considered as error data.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions