title: Bulk Insert using format file for varying number of columns between datafile and actual database table
body: <p>This is the Schema for a table</p>
  
  <pre><code>Create table dbo.Project
  (
  ProjectID (int,not null)
  ManagerID (int,not null)
  CompanyID(int, not null)
  Title (nvarchar(50),not null)
  StartDate(datetime,not null)
  EndDate(datetime,null)
  ProjDescription(nvarchar(max))
  )
  </code></pre>
  
  <p>I created a datafile called bob.dat from this table which has around 15 rows with the following bcp command</p>
  
  <pre><code>bcp "Select ProjectID,ManagerID,CompanyID,Title,StartDate from CATS.dbo.Project" queryout "C:\Documents\bob.dat" -Sbob-pc -T -n 
  </code></pre>
  
  <p>Also a format/mapping file called bob.fmt was created using the following bcp command</p>
  
  <pre><code>bcp CATS.dbo.Project format nul -f C:\Documents\bob.fmt -x -Sbob-pc -T -n 
  </code></pre>
  
  <p>Then i created a copy of the table Project.</p>
  
  <pre><code>Create table dbo.ProjectCopy
  (
  ProjectID (int,not null)
  ManagerID (int,not null)
  CompanyID(int, not null)
  Title (nvarchar(50),not null)
  StartDate(datetime,not null)
  EndDate(datetime,null)
  ProjDescription(nvarchar(max))
  </code></pre>
  
  <p>)</p>
  
  <p>What i want to do now is use the bob.dat and bob.format file to populate this table ProjectCopy using the following Bulk Insert statement.</p>
  
  <pre><code>BULK INSERT CATS.dbo.ProjectCopy
   FROM 'C:\Documents\bob.dat' 
  WITH (FORMATFILE = 'C:\Documents\bob.fmt',
   LASTROW=5,
   KEEPNULLS,
    DATAFILETYPE='native');
    GO
   SELECT * FROM CATS.dbo.ProjectCopy
   GO
  </code></pre>
  
  <p>So basically the data file does not contain any data for the columns EndDate and ProjDescription. I want these two columns to be remained as null. Unfortunately i get the
  following error when i run the bulk insert statement.</p>
  
  <pre><code> Msg 4863, Level 16, State 4, Line 2
   Bulk load data conversion error (truncation) for row 1, column 6 (EndDate).
   Msg 7399, Level 16, State 1, Line 2
   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 2
   Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
  
   (0 row(s) affected)
  </code></pre>
  
  <p>Anyone got any clue how this could be fixed?
  just to inform you all i have already been to this sections and the solution provided there
  didn't work out for me.
  <a href="https://stackoverflow.com/questions/2601941/bulk-insert-with-inconsistent-number-of-columns">BULK INSERT with inconsistent number of columns</a>,
  <a href="https://stackoverflow.com/questions/7667397/cant-identify-reason-for-bulk-insert-errors?rq=1">Can&#39;t identify reason for BULK INSERT errors</a>,
  <a href="https://stackoverflow.com/questions/2601941/bulk-insert-with-inconsistent-number-of-columns">BULK INSERT with inconsistent number of columns</a></p>