Bulk Insert Failed “Bulk load data conversion erro

Question

I've done data imports with SQL Server's BULK INSERT task hundreds of times, but this time I'm receiving an error that's unfamiliar and that I've tried troubleshooting to no avail with Google. The below is the code I use with a comma deliminated file where the new rows are indicated by new line characters:

BULK INSERT MyTable
FROM 'C:\myflatfile.txt'
WITH (
    FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '/n')
GO

It consistently works, yet now on a simple file with a date and rate, it's failing with the error of "Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 2 (ColumnTwo)." When I look at the file, I don't see why this would fail (usually the Google troubleshooting indicates that the delimiters may exist multiple times in a line, which would raise this error). From the file, here are the first ten lines (note that it fails on the FIRST line):

1961-01-01,8.2
1961-02-01,8.2
1961-03-01,7.4
1961-04-01,7.6
1961-05-01,7.8
1961-06-01,8.5
1961-07-01,9.1
1961-08-01,8.8
1961-09-01,8.4
1961-10-01,8.8

The table I'm inserting these data into has two fields thare are VARCHAR(50), even though when I initially saw the truncation I expanded the data fields to VARCHAR(2000) and it didn't affect it.

CREATE TABLE MyTable (
    ColumnOne VARCHAR(50),
    ColumnTwo VARCHAR(50)
)

I also tried removing all the dashes to see if that was messing up things (even though I've done plenty of data imports with dashes using this same code and it works without error), and it still received the same error message.

A direct import works (through Tasks) as does SSIS, but what about this code is failing, as it should be doing the exact same thing?

Answer 1

The problem is likely the row terminator is not working due to the file format.

Try:

ROWTERMINATOR = '0x0a'

EDIT

Actually I just notice you are using forward slash, it should be backslash, so this may work:

ROWTERMINATOR = '\n'

Answer 2

From SQL Server Management Studio (SSMS), for a Unix-style file, ROWTERMINATOR = '0x0a' works. However, ROWTERMINATOR = '\n' does not since SSMS apparently interprets the \n as a Windows-style end of line sequence (\r\n) and fixes/breaks it for you.

Interestingly, if you send the same ROWTERMINATOR = '\n' from Java code through the SQL Server JDBC driver, it will be seen as the Unix-style end-of-line since nothing in the middle throws in the extra \r.

So, you have to do two things:

1 - Make sure you understand how your data file actually does end-of-line.

2 - Make sure you understand how your means of delivering the BULK INSERT sql to SQL Server is interpreting any escape sequences. My limited experience is that using hex ('0x0a') for SQL Server SQL works in all environments.