Home Dashboard Directory Help

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


 as Fixed Help for as Fixed

Sign in
to vote
Type: Bug
ID: 331605
Opened: 3/5/2008 12:59:08 PM
Access Restriction: Public
User(s) can reproduce this bug


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.
Sign in to post a workaround.
Posted by damianyoung on 10/7/2009 at 1:55 AM
I suggest checking your column numbering for duplicate columns if you hit this issue.
File Name Submitted By Submitted On File Size  
ImportFormat.txt (restricted) 3/5/2008 -
data.txt (restricted) 3/5/2008 -
Create_Table.txt (restricted) 3/5/2008 -