SSMS Import Flat File fails to import all data
In SQL Server Management Studio 18.0 and 18.1, the following sample file defaults to INT NULL for the data type of the ID field and imports NULL for ID = 'abc'. This happens even if the data type of the field is set to NVARCHAR(50) NULL in the Modify Columns step. In this case the data type for ID in the created table is as requested, not INT NULL. If the data type is set to NVARCHAR(50) NOT NULL, it will generate an error (column 'Id' does not allow DBNULL). The expected behavior is to import all ID values as text when the type is set to NVARCHAR(50).
A CSV File is attached, and copied below (I hope the formatting works)
Aside #1: SSMS 17.x worked as expected.
Aside #2: if there are sufficient number of non-int IDs (or maybe it is a ratio of INTs to text, the analyzer will default to NVARCHAR(50), and all is well.
Aside #3: If the last record is removed, the the tool treat the first row as data (instead of column names as expected) and then fails on the first row or imports NULL for column1 = 'id'