Bulk Insert with format file NOT skipping column in destination table with 146 fields as it should be : <p>Here is the full error:</p>

<p>Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (FK_User_CreatedBy).</p>

<p>And here is the existential snapshot of my pain :)</p>

<p><a href="https://i.stack.imgur.com/lEEYd.png" rel="noreferrer"><img src="https://i.stack.imgur.com/lEEYd.png" alt="enter image description here"></a></p>

<p><strong>Many questions touch on these issues, <a href="https://msdn.microsoft.com/en-gb/library/ms179250.aspx" rel="noreferrer">but none of them do the trick...</a></strong></p>

<p>I suspect my problem is something like described <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3e6ac2df-5f80-4bc9-bce3-7009d7908400/how-to-bulk-insert-rows-from-text-file-into-a-wide-table-which-has-1400-columns?forum=transactsql" rel="noreferrer">here</a>, but I am not sure. The destination table column that is not being skipped properly is NOT sparse.</p>

<p><a href="https://i.stack.imgur.com/ATGPs.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ATGPs.png" alt="enter image description here"></a></p>

<p>Here is the two row data file for import (.csv) open in notepad and notepad++:
(Yes I am aware that the row terminator is \r\n and the field/column terminator is \t or ',')</p>

<p><a href="https://i.stack.imgur.com/tlFK3.png" rel="noreferrer"><img src="https://i.stack.imgur.com/tlFK3.png" alt="enter image description here"></a></p>

<p>Here it is in plain text:</p>

<pre><code>1,fArty,Padul,1,10/1/1962,Head of ,Australia,AU Talavera Centre,NSW,7 CSU,farty.randy@gummibaer.com
2,mifsm,Jodel,1,10/1/1970,Chief Officer,Australia,AU ,NSW,8 CSU,midsm@gummibaer.com
</code></pre>

<p><strong>CONTEXT/BACKGROUND:</strong>
<strong>Test on Small Table and Input File with Few Records</strong> (remember it is column skipping on a table with many columns that ends up hurting)...</p>

<p>The import WORKS perfectly for a small database table that looks like this:</p>

<p><a href="https://i.stack.imgur.com/UCtNW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/UCtNW.png" alt="enter image description here"></a></p>

<p>And is created thus:</p>

<p><a href="https://i.stack.imgur.com/32e3r.png" rel="noreferrer"><img src="https://i.stack.imgur.com/32e3r.png" alt="enter image description here"></a></p>

<p>Here is the code for the table create:</p>

<pre><code>    DROP TABLE dbo.tbl_Person_Importtest

CREATE TABLE dbo.tbl_Person_Importtest 
(
ID int PRIMARY KEY NOT NULL,
LastName varchar(100) NOT NULL, 
FirstName varchar(100) NOT NULL, 
FK_Gender varchar(4) NOT NULL, 
DateOfBirth date NOT NULL, 
JobTitle varchar(200) NOT NULL, 
Address1Country varchar(50) NOT NULL, 
Location varchar(200) NOT NULL,
Address1StateOrProvince varchar(50) NOT NULL, 
Department varchar(200) NOT NULL, 
EMailAddress1  varchar(200) NOT NULL
)  
</code></pre>

<p>The .xml bulk insert format file looks like this:</p>

<p><a href="https://i.stack.imgur.com/eiroi.png" rel="noreferrer"><img src="https://i.stack.imgur.com/eiroi.png" alt="enter image description here"></a></p>

<p>Note that it also works if I skip the ID (PK + index) column since the database table is empty and the import file does not have an index. This is working fine for the small destination table, as the database is generating the primary key index.</p>

<p>Here the format file as text ():</p>

<pre><code>&lt;?xml version="1.0"?&gt;
&lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
 &lt;RECORD&gt;
  &lt;FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/&gt;
  &lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="11"/&gt;
  &lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/&gt;
  &lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/&gt;
 &lt;/RECORD&gt;
 &lt;ROW&gt;
  &lt;COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/&gt;
  &lt;COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="4" NAME="FK_Gender" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="5" NAME="DateOfBirth" xsi:type="SQLDATE"/&gt;
  &lt;COLUMN SOURCE="6" NAME="JobTitle" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="7" NAME="Address1Country" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="8" NAME="Location" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="9" NAME="Address1StateOrProvince" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="10" NAME="Department" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="11" NAME="EMailAddress1" xsi:type="SQLVARYCHAR"/&gt;
 &lt;/ROW&gt;
&lt;/BCPFORMAT&gt;
</code></pre>

<p>And it was created using bcp at the command line like this:</p>

<p><a href="https://i.stack.imgur.com/UTxfz.png" rel="noreferrer"><img src="https://i.stack.imgur.com/UTxfz.png" alt="enter image description here"></a></p>

<p>Here is the bcp command line in text:</p>

<p>bcp YFP..tbl_Person_Importtest format nul -f PersonImportMapFile.xml -c -x -T</p>

<p>Now when I execute the import with all of these files against the empty small table, all is good:</p>

<p><a href="https://i.stack.imgur.com/jnjNu.png" rel="noreferrer"><img src="https://i.stack.imgur.com/jnjNu.png" alt="enter image description here"></a></p>

<p>If I insert more rows again, no problem...
<a href="https://i.stack.imgur.com/SoCQs.png" rel="noreferrer"><img src="https://i.stack.imgur.com/SoCQs.png" alt="enter image description here"></a>
<a href="https://i.stack.imgur.com/CNOzC.png" rel="noreferrer"><img src="https://i.stack.imgur.com/CNOzC.png" alt="enter image description here"></a></p>

<p><strong>THE LARGE TABLE</strong> I cannot include a full description due to intellectual property issues, but the large destination table has 146 fields with no sparse fields and plenty of DATETIME and DATE fields, as well as stacks of foreign keys (mostly INT) some of which are nullable. Here is the map file as generated by bcp (With Field names truncated and some removed):</p>

<pre><code>   CREATE TABLE [dbo].[tbl_Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordTitle] [nvarchar](250) NULL,
[SecurityCode] [nvarchar](250) NULL,
[DateCreated] [smalldatetime] NOT NULL,
[FK_User_CreatedBy] [int] NULL,
[wning] [int] NULL,
[ssigned] [int] NULL,
[ollowup] [int] NULL,
[sation_Owning] [int] NULL,
[wning] [int] NULL,
[pdate] [smalldatetime] NULL,
[astUpdate] [int] NULL,
[tatus] [bit] NULL,
[ive] [smalldatetime] NULL,
[eason] [nvarchar](250) NULL,
[tatus] [bit] NULL,
[ion] [smalldatetime] NULL,
[Titles] [int] NULL,
[LastName] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[ion] [ntext] NULL,
[Code] [nvarchar](50) NULL,
[r] [int] NULL,
[] [nvarchar](100) NULL,
[nt] [nvarchar](100) NULL,
[ame] [nvarchar](100) NULL,
[hone] [nvarchar](50) NULL,
[tName] [nvarchar](100) NULL,
[e1] [nvarchar](50) NULL,
[e2] [nvarchar](50) NULL,
[one1] [nvarchar](50) NULL,
[Moe2] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[e1] [nvarchar](250) NULL,
[e2] [nvarchar](250) NULL,
[e3] [nvarchar](250) NULL,
[Address1CityOrSuburb] [nvarchar](50) NULL,
[Address1StateOrProvince] [nvarchar](50) NULL,
[Address1Country] [nvarchar](50) NULL,
[Address1PostalCode] [nvarchar](20) NULL,
[Line1] [nvarchar](250) NULL,
[Line2] [nvarchar](250) NULL,
[Line3] [nvarchar](250) NULL,
[CityOrSuburb] [nvarchar](50) NULL,
[StateOrProvince] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[PostalCode] [nvarchar](20) NULL,
[RL] [nvarchar](200) NULL,
[ress1] [nvarchar](100) NULL,
[ress2] [nvarchar](100) NULL,
[ne] [bit] NULL,
[] [bit] NULL,
[il] [bit] NULL,
[tail] [bit] NULL,
[kEl] [bit] NULL,
[kPalMail] [bit] NULL,
[dMM] [bit] NULL,
[_Preferred] [int] NULL,
[] [int] NULL,
[onStatus] [int] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[6] [money] NULL,
[ncome] [money] NULL,
[rInc1] [money] NULL,
[rInc2] [money] NULL,
[rInc3] [money] NULL,
[rInc4] [money] NULL,
[rInc5] [money] NULL,
[rInc6] [money] NULL,
[artner] [money] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[6] [money] NULL,
[7] [money] NULL,
[8] [money] NULL,
[ud] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[lAss] [money] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[lDebt] [money] NULL,
[rganisation_Provider] [int] NULL,
[Insurance] [money] NULL,
[ver] [money] NULL,
[itd] [nvarchar](250) NULL,
[veod] [nvarchar](250) NULL,
[fiNominated] [bit] NULL,
[ [money] NULL,
[idD] [nvarchar](50) NULL,
[ccs] [int] NULL,
[mpus] [int] NULL,
[ry] [money] NULL,
[feInsurance] [bit] NULL,
[Cor] [bit] NULL,
[ov] [money] NULL,
[DCer] [bit] NULL,
[mous] [int] NULL,
[iftatus] [int] NULL,
[PCos] [int] NULL,
[PDCus] [int] NULL,
[ersned] [int] NULL,
[ueKey] [uniqueidentifier] NULL,
[rified] [bit] NULL,
[Actr] [smalldatetime] NULL,
[embpe] [int] NULL,
[etAult] [money] NULL,
[t7] [money] NULL,
[t8] [money] NULL,
[6] [money] NULL,
[7] [money] NULL,
[8] [money] NULL,
[onalScore] [nvarchar](10) NULL,
[] [int] NULL,
[rganment] [int] NULL,
[rac] [int] NULL,
[kerpdate] [datetime] NULL,
[keriew] [datetime] NULL,
[ari] [int] NULL,
[] [int] NULL,
[Q1] [int] NULL,
[Q2] [int] NULL,
[Q3] [int] NULL,
[Q4] [int] NULL,
[Q5] [int] NULL,
[Q6] [int] NULL,
[Q7] [int] NULL,
[Q8] [int] NULL,
[Q9] [int] NULL,
[Q10] [int] NULL,
 CONSTRAINT [PK_tbl_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</code></pre>

<p><strong>Outcome</strong></p>

<p>I should be able to import into this table using the same data file as I specified for the example with the smaller table above, but I am getting the error specified at the beginning of this question.</p>

<p>The field it is picking on is indeed the fifth field/column in the table, but it is supposed to be skipping to the fields named in the map only, according to <a href="https://msdn.microsoft.com/en-gb/library/ms179250.aspx" rel="noreferrer">this MS tutorial</a>.</p>

<p>IT just looks like I will need to use a staging table or other programmatic approach wit middleware or SQLBulkCopy (c# .NET), and I would prefer not to do this at this stage. I would just like the map file to work.</p>

<p><strong>Did I miss something, or is it a case of shoot the BULK INSERT with-map-file for-large-table horse and get a different ride?</strong></p>
