title: My last column isn't getting populated
body: <p>I'm trying to use a non-xml format file to bulk import a null delimited file into sql. I've added a column to the staging table in question, and updated the format file to reflect this. Everything seems to be inserting fine, except this last column. The column I added is </p>
  
  <pre><code>Comments (nvarchar(256), null)
  </code></pre>
  
  <p>The format file looks like this:</p>
  
  <pre><code>11.0
  8
  1   SQLNCHAR    0   4   "\0\0"  1   ClaimCheckSetId ""
  2   SQLNCHAR    0   4   "\0\0"  2   BatchValidationId   ""
  3   SQLNCHAR    0   4   "\0\0"  3   SourceCommunicationId   ""
  4   SQLNCHAR    0   4   "\0\0"  4   TargetCommunicationId   ""
  5   SQLNCHAR    0   1800    "\0\0"  5   TargetExternalCommunicationId   ""
  6   SQLNCHAR    0   8   "\0\0"  6   TargetSentDateTime  ""
  7   SQLNCHAR    0   2000    "\0\0"  7   TargetSubject   ""
  8   SQLNCHAR    0   256 "\r\0\n\0"  8   Comments ""
  </code></pre>
  
  <p>The SQL looks like this:</p>
  
  <pre><code>DECLARE @filepath NVARCHAR(MAX) = 'C:\{file to import}_512fc21d-dbc9-4975-8169-2ca383ac2bdf.txt';
  DECLARE @formatpath NVARCHAR(MAX) = 'C:\{format file}.txt';
  DECLARE @bulkinsert NVARCHAR(MAX);
  
  SET
          @bulkinsert = 
          N'BULK INSERT 
              [The Table]
              FROM ''' +
              @filepath + N''' 
              WITH 
              (
                  FORMATFILE = ''' +
                  @formatpath + N''',
                  DATAFILETYPE = ''WIDECHAR'',
                  FIRSTROW = 1
              )';
  
      SET ANSI_WARNINGS OFF;
      EXEC sp_executesql @Bulkinsert;
      SET ANSI_WARNINGS ON;
  </code></pre>
  
  <p>I'm getting no errors, and it is returning a number of rows affected. Unfortunately, I don't know enough about SQL to diagnose this problem. A few hours of googling have not helped either. I hope one of you kind guys or gals can set me back on the straight and narrow.</p>
  
  <p>Update: I edited the \r\0\n\0 to \r\n and am now getting an error!</p>
  
  <pre><code>OLE DB provider 'BULK' for linked server '(null)' returned invalid data for column '[BULK].InsertedDateTime'.
  </code></pre>