Bulk Insert Failed "Bulk load data conversion error (truncation)"

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'