Bad error message when format file has incorrect column numbers. - by Erland Sommarskog

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 331605 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 3/5/2008 12:59:08 PM
Access Restriction Public


The attached format file has an error:

   234   SQLCHAR  0  9  "" 197   FIELD197 SQL_Latin1_General_CP1_CI_AS
   235   SQLCHAR  0  10 "" 198   FIELD198 SQL_Latin1_General_CP1_CI_AS
   236   SQLCHAR  0  1  "" 198   FIELD199 SQL_Latin1_General_CP1_CI_AS
   237   SQLCHAR  0  9  "" 200   FIELD200 SQL_Latin1_General_CP1_CI_AS

Note that the column number 198 appears twice.

When you try to bulk-load the file on SQL 2005 and SQL 2008, you get an error

   Msg 4828, Level 16, State 1, Line 1
   Cannot bulk load. Invalid destination table column number for source column 271 in the    
   format file "c:\temp\ImportFormat.txt".

I picked up this issue from the newsgroup where a user could not understand the message, since it did not point anywhere close to the error. To make it even more
confusing, he had had the format file working without an error on SQL 2000.
Sign in to post a comment.
Posted by damianyoung on 10/7/2009 at 1:54 AM
Had the same issue. If it was'nt for this post I would not have found the issue. SSIS reported issue with column 11, issue was at column 102 which was listed as 10 in the format file.

This job was also running in 2000.
Posted by Microsoft on 3/10/2008 at 10:18 AM
thanks for reporting this issue. It is being investigated.