Starting in SQL Server 2019 CU6, the Create External Tabledefinition has been extended to include a Rejected_Row_Location parameter. Thisvalue represents the location in the External Data Source in which the ErrorFiles and Rejected Rows will be written. This option can be used only together withHadoop type data sources.
CREATE EXTERNAL TABLE[dbo].[Reject_Example]([Col_one] TINYINT NULL,[Col_two] VARCHAR(100) NULL,[Col_three] NUMERIC(2,2) NULL)WITH(DATA_SOURCE = EDS_Reject_Row,LOCATION = '/Read_Directory',FILE_FORMAT = CSV,REJECT_TYPE = VALUE,REJECT_VALUE = 100,REJECTED_ROW_LOCATION=‘/Reject_Directory')
Whathappens when data is loaded?
When a user runs a Create Table as Select(CTAS) on the previous table, PolyBase creates a directory on the External DataSource at the Rejected_Row_Location, if one doesn’t exist. A child directory iscreated that is named "_rejectedrows." The Underscore (_) character makes sure that thedirectory is escaped for other data processing, unless explicitly named in thelocation parameter. Within this directory, a folder is created based onthe time of load submission in the format YearMonthDay-HourMinuteSecond (for example, 20180330-173205). In this folder, two types of files are written: The _reasonfile and the data file.
The reason file and the data file bothhave the queryID associated with the CTAS statement. Because the data and thereason are in separate files, corresponding files have a matching suffix.
Microsoft has added new features that are listed in the "Applies to" section.
This issue is fixed in the following cumulative update for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server: