I am running a Bulk Insert from a fixed length text file. The following code works well as long the text file contains some data for all of the fields. The problem occurs when a row in the text file is missing some values for the last couple of fields.

truncate table im_ag

bulk insert im_ag
from 'c:\temp\mydata.txt'
with (formatfile='c:\myformatfile.fmt')

This is what my text file looks like:

1000031951AON20020226012112664000MM C01O20020828U 00000000
1000031951AON20020226012112664000NA R01E20020828 20031130
1168918987APN2002090901175946401
1000002284AON20020412011770464010FS 01O20020805NN20021231
1000002284AON2002041201177046401 MM S01P20020618D 00000000

The third row in this file is missing data and this is where the job fails.

Is there any way around this?