Data Cogs Information Technology

I had a problem that left me scratching my head for a while this morning.  I had a format file that I was trying to use with a Bulk Insert (bcp) task that generated looking like this:

7.0
58
1 SQLCHAR 0 9 "" 1 Column Name With Space 1
2 SQLCHAR 0 1 "" 2  Column Name With Space 2
3 SQLCHAR 0 10 "" 3  Column Name With Space 3
4 SQLCHAR 0 9 "" 4  Column Name With Space 4
5 SQLCHAR 0 9 "" 5  Column Name With Space 5
etc...
58 SQLCHAR 0 54 "\r\n" 58  Column Name With Space 58

But when I tried to run the DTS Package with the Bulk Insert Task, I got this error...

“Could not bulk insert.  Invalid column number in format file XXX.“

After a bit of trial and error I concluded that it was the spaces in the field names that was causing the problem.  The trick is that the column names in the format file are actually just placeholders and they have nothing to do with the column names in the actual destination table.  So I modified the format file to look like this (just need to take the spaces out of the column names in the format file):

7.0
58
1 SQLCHAR 0 9 "" 1 a
2 SQLCHAR 0 1 "" 2 b
3 SQLCHAR 0 10 "" 3 c
4 SQLCHAR 0 9 "" 4 d
5 SQLCHAR 0 9 "" 5 e
etc...
58 SQLCHAR 0 54 "\r\n" 58 fff

Then I got this error:

“Could not bulk insert.  Error reading destination table column for source column 58 in format file XXX“

Finally, I added a carriage return line feed at the end of the format file and everything worked fine.