Fixed length Bulk Import with BCP format file

Question

I have a huge fixed length flat file with no column delimiters, I am trying to port this data into a table using BCP utility.

Below is my sample format I created with BCP :

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NCharFixed"   LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharFixed"  LENGTH="9"/>
  <FIELD ID="3" xsi:type="NCharFixed"   LENGTH="20"/>
  <FIELD ID="4" xsi:type="NCharFixed"   LENGTH="4"/>
  <FIELD ID="5" xsi:type="NCharFixed"  LENGTH="15"/>
  <FIELD ID="6" xsi:type="NCharFixed"   LENGTH="15"/>
  <FIELD ID="7" xsi:type="NCharFixed"   LENGTH="1"/>
  <FIELD ID="8" xsi:type="NCharFixed"  LENGTH="8"/>
  <FIELD ID="9" xsi:type="NCharFixed"  LENGTH="8"/>
  <FIELD ID="10" xsi:type="NCharTerm" TERMINATOR="\r\n" LENGTH="19"/>

 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BACD"  xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="2" NAME="SSN" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="3" NAME="LASTNAME" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="4" NAME="suffix" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="5" NAME="firstname" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="6" NAME="middlename" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="7" NAME="code" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="8" NAME="dod" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="9" NAME="dob" xsi:type="SQLNCHAR" />
  <COLUMN SOURCE="10" NAME="blank" xsi:type="SQLNCHAR" />

 </ROW>
</BCPFORMAT>

My table design: BACD nchar(1), SSN nchar(9), Lastname nchar(20), suffix nchar(4), firstname nchar(15), middlename nchar(15), code nchar(1), dod nchar(8), dob nchar(8), blank nchar(19)

Sample data: 005070002Foobar FOOOO 1100577505671903

SQL query:

BULK INSERT sample.dbo.DD FROM 'C:\sample-for-test.txt'
WITH (FORMATFILE = 'C:\format-n.xml', ROWTERMINATOR='\r\n');
SELECT * FROM sample.dbo.DD;

Error I am getting is : Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (BACD). Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I checked the BACD column, the length is 1 character and the value in this case a space. All the columns allow nulls. Can anyone let me know what I am doing wrong? and why am i getting this error?

Answer 1

Well it worked ... changed NCharFixed to CharFixed

Answer 2

I haven't looked at SQL Server stuff in years but my knee-jerk answer is that you need to define a primary key in the destination table. If you have one, maybe non-unique items in your BACD column are violating an existing index rule.

Let me know if this helps at all.