Bulk Insert with XML format file - not skipping columns

[Jim]

Good morning,

I am receiving a fixed width flat file from a vendor. I am needing to
bulk import it into a SQL 2005 table. My problem is that my table
starts with some tracking fields that are not part of the vendor
file. I need to skip those fields when I import the data. But for
some reason, the server is not skipping the fields. I have read
everything I could in the BOL, and googled myself to death. I am
including a some sample codes that reproduce the problem. If anyone
could give any ideas, I would greatly appreciate it.

Thank you

Jim

CREATE TABLE:
CREATE TABLE [dbo].[ImportTest](
[ImportID] [int] IDENTITY(1,1) NOT NULL, -- mine
[LoadID] [int] NULL, -- mine, to be
filled in later
[AdminKey] [varchar](9) NULL, -- from import
[CustomerKey] [varchar](9) NULL, -- from import
[TransactionType] [varchar](1) NULL, -- from import
[SubscriberID] [varchar](36) NULL, -- from import
PRIMARY KEY CLUSTERED -- this is from the table I have in
production,
-- I have no idea if
it's needed or the cause of my problem
(
[ImportID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY]
) ON [PRIMARY]


DATA.TXT:
000000001000000002Aa1b2c3d4e5f6g7h8i9j0klmnopqrstuvwxyz


Import_Format.XML:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="F1" xsi:type="CharFixed" LENGTH="9"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F2" xsi:type="CharFixed" LENGTH="9"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F3" xsi:type="CharFixed" LENGTH="1"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F4" xsi:type="CharFixed" LENGTH="36"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F5" xsi:type="CharTerm" TERMINATOR="\r\n"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
</RECORD>
<ROW>
<COLUMN SOURCE="F1" NAME="AdminPartyKey" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F2" NAME="CustomerPartyKey" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F3" NAME="TransactionType" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F4" NAME="SubscriberID" xsi:type="SQLCHAR" />
</ROW>
</BCPFORMAT>


BULK INSERT STATEMENT:

BULK INSERT ImportTest
FROM '\\lousqlwts04\FTPROOT\ADSCDS\ACSFiles\data.txt'
WITH (FORMATFILE = '\\lousqlwts04\FTPROOT\ADSCDS\Settings
\Import_Format.XML',
DATAFILETYPE = 'char'
);


What I Expect:
ImportID, LoadID,AdminKey , CustomerKey,TransactionType,SubscriberID
1 NULL 000000000 000000000 A
a1b2c3d4e5f6g7h8i9j0klmnopqrstuvwxyz

What I Get:
ImportID, LoadID,AdminKey , CustomerKey,TransactionType,SubscriberID
1 0 A a1b2c3d4e

[Jim]

I'm sorry, the XML should be:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="F1" xsi:type="CharFixed" LENGTH="9"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F2" xsi:type="CharFixed" LENGTH="9"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F3" xsi:type="CharFixed" LENGTH="1"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F4" xsi:type="CharFixed" LENGTH="36"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="F5" xsi:type="CharTerm" TERMINATOR="\r\n"
COLLATION="SQL_Latin1_General_CP1_CI_AS" />
</RECORD>
<ROW>
<COLUMN SOURCE="F1" NAME="AdminKey" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F2" NAME="CustomerKey" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F3" NAME="TransactionType" xsi:type="SQLCHAR" />
<COLUMN SOURCE="F4" NAME="SubscriberID" xsi:type="SQLCHAR" />
</ROW>
</BCPFORMAT>

[Erland Sommarskog]

Post by Jim
I am receiving a fixed width flat file from a vendor. I am needing to
bulk import it into a SQL 2005 table. My problem is that my table
starts with some tracking fields that are not part of the vendor
file. I need to skip those fields when I import the data. But for
some reason, the server is not skipping the fields. I have read
everything I could in the BOL, and googled myself to death. I am
including a some sample codes that reproduce the problem. If anyone
could give any ideas, I would greatly appreciate it.

I have never understood the point of the new XML format. Seems just like
a bulkier way of saying would could say with the old format. And the
old format I speak fluently, so try this format file:

9.0
5
1 SQLCHAR 0 9 "" 3 AdminKey ""
2 SQLCHAR 0 9 "" 4 CustomerKey ""
3 SQLCHAR 0 1 "" 5 TransactionType ""
4 SQLCHAR 0 36 "" 6 SubscriberID ""
5 SQLCHAR 0 0 "\r\n" 0 "" ""

Important to know is that the column name here is informational only. What
matters is the column number. 0 means "don't import this field in the
host file".

[Jim]

Maybe I need to take a step back then. This whole thing started when
we upgraded from SQL 2000 to 2005. Our original format file is in the
older format:
8.0
545
1 SQLCHAR 0 9 "" 3 FirstField SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 9 "" 4 SecondField SQL_Latin1_General_CP1_CI_AS
...
544 SQLCHAR 0 8 "" 473 NextToLast SQL_Latin1_General_CP1_CI_AS
545 SQLCHAR 0 6 "\r\n" 474 LastField SQL_Latin1_General_CP1_CI_AS

As you can see, it is quite large. It works perfectly in 2000.
However, 2005 keeps throwing 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.

If I go to column 271 and zero it out ( the "do not import"
indicator ) then it throws on 272. Zero 272 and 273 is thrown. Etc.,
etc.

Any pointers?

Thanks,
Jim

[Erland Sommarskog]

Post by Jim
Maybe I need to take a step back then. This whole thing started when
we upgraded from SQL 2000 to 2005. Our original format file is in the
8.0
545
1 SQLCHAR 0 9 "" 3 FirstField SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 9 "" 4 SecondField SQL_Latin1_General_CP1_CI_AS
...
544 SQLCHAR 0 8 "" 473 NextToLast SQL_Latin1_General_CP1_CI_AS
545 SQLCHAR 0 6 "\r\n" 474 LastField SQL_Latin1_General_CP1_CI_AS
As you can see, it is quite large. It works perfectly in 2000.
Msg 4828, Level 16, State 1, Line 1
Cannot bulk load. Invalid destination table column number for source
column 271 in the format file.
If I go to column 271 and zero it out ( the "do not import"
indicator ) then it throws on 272. Zero 272 and 273 is thrown. Etc.,
etc.

The inevitable question is of course: are you sure that there are more
than 271 columns in the destination table? Or, hm, you are skipping
some columns. What is the table-column number for source-column 271?
If it's 256 I get an uncanny feeling...

Would it be possible for you to post a complete repro? That is, the
total format file, the CREATE TABLE statement for the table, the BULK
INSERT command you use and a sample datafile. If you don't want to
share it in public, feel free to mail me. Whatever, put the datafile in
an attachment so it arrives unscathed.

[Erland Sommarskog]

Post by Jim
Maybe I need to take a step back then. This whole thing started when
we upgraded from SQL 2000 to 2005. Our original format file is in the
8.0
545
1 SQLCHAR 0 9 "" 3 FirstField SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 9 "" 4 SecondField SQL_Latin1_General_CP1_CI_AS
...
544 SQLCHAR 0 8 "" 473 NextToLast SQL_Latin1_General_CP1_CI_AS
545 SQLCHAR 0 6 "\r\n" 474 LastField SQL_Latin1_General_CP1_CI_AS
As you can see, it is quite large. It works perfectly in 2000.
Msg 4828, Level 16, State 1, Line 1
Cannot bulk load. Invalid destination table column number for source
column 271 in the format file.
If I go to column 271 and zero it out ( the "do not import"
indicator ) then it throws on 272. Zero 272 and 273 is thrown. Etc.,
etc.

Just a quick followup on this. Jim was kind to mail me table definition,
format file and a sample data file.

After examining the situation I found that the root cause was this error
in the format file:

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

Notice that 198 appears twice. The fact that this file was accepted by
BULK INSERT in SQL 2000, is clearly a bug. The file is also accepted by
BCP in SQL 2000 and SQL 2005. However, BCP in SQL 2008 rejects it.

But the error message Jim is of course in correct, and I have filed a
bug on Connect for this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331605

While playing around more with it, I found that BULK INSERT on SQL 2005
and SQL 2008 accepts a format file that specifies a column that does
not exist. BCP barfs at this (with an incomprehensible error message),
and so does BULK INSERT in SQL 2000 (with a clear error message).
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331611