Bulk Insert is throwing an error

Question

I have a source file with ".dat" extension and want to load it into as table in SSMS. My Code below is here:

But is gives me the following error. I also opened the file in Notepad++ to check the ROWTERMINATOR, which appeared to be LF.

Msg 4866, Level 16, State 8, Line 34 The bulk load failed. The column is too long in the data file for row 1, column 25. Verify that the field terminator and row terminator are specified correctly. Msg 7301, Level 16, State 2, Line 34 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

Code:

USE myDB
GO
CREATE TABLE [dbo].[myTable]

(
Column1 NVARCHAR(255) NULL,
Column2 NVARCHAR(255) NULL,
Column3 NVARCHAR(100) NULL,
Column4 NVARCHAR(255) NULL,
Column5 NVARCHAR(255) NULL,
Column6 NVARCHAR(255) NULL,
Column7  NVARCHAR(255) NULL,
Column8  NVARCHAR(255) NULL,
Column9  NVARCHAR(255) NULL,
Column10  NVARCHAR(255) NULL,
Column11  NVARCHAR(255) NULL,
Column12 NVARCHAR(255) NULL,
Column13  NVARCHAR(255) NULL,
Column14 NVARCHAR(255) NULL,
Column15  NVARCHAR(255) NULL,
Column16 NVARCHAR(255) NULL,
Column17  NVARCHAR(255) NULL,
Column18  NVARCHAR(255) NULL,
Column19  NVARCHAR(255) NULL,
Column20  NVARCHAR(255) NULL,
Column21  NVARCHAR(255) NULL,
Column22 NVARCHAR(255) NULL,
Column23 NVARCHAR(255) NULL,
Column24 NVARCHAR(255) NULL,
Column25 NVARCHAR(255) NULL
);

BULK 
INSERT [dbo].[myTable]
FROM 'C:\Users\John\Documents\work\MyFile.dat'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);
SELECT * FROM [dbo].[myTable]

Below is the sample of the data and that very row that SSMS is failing:

Kansas City 1 494124 20 McDonalds KANSAS CUSTOMERS 22 WYANDOTTE COUNTY 239 Wyandotte County 964 S 73RD ST KANSAS CITY KS 66111 2805 Y Y Y N N 4 D Old School Loyal N 49412401

Answer 1

be sure your user database authentication have access to bulk insert? If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.

if you have access to bulk insert to database check your datatype and datalen in your dat file

Answer 2

ROWTERMINATOR = "0x0a"

For the win!

Answer 3

It's a common problem where I work. If you have line feed endings, and, logically use ROWTERMINATOR = '\n', bcp adds \r to it:

When you use bcp interactively and specify \n (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r\n. (Source)

I'm not sure it helps in your case, but if I use ROWTERMINATOR = "0x0a" it works perfectly fine.

Edit

In your comment you say you are trying to insert 25 columns into a 9 column table. Obviously that's not going to work. You should fix the number of columns first.