Bulk Insert Task - Where Does The First Row Begin?

Hi,

I am using the Bulk Insert Task to bulk copy data from a flat file to a SQL Server 2005 table. The flat file contains pipe ( | ) delimited columns and several thousand records which are {CR}{LF} delimited. The file also contains a header record and trailer record, which contain several words but also contains a ( | ) symbol among those words.

E.g.:

HEDR | yadi yadi yada
500 | Data Data | More Data | 600
460 | Datum | More More | 705
550 | Data | Data | 603
FOOTR | yadi yadi yada

I need to ignore the header and trailer records and just pickup the proper records. But even though I manually set the First Row property to 2 and the Last Row property to 4, It does not pickup the first true record i.e. the record which begins with 500, and if I set the First Row to 1, it throws me an error citing 'truncation error' or similar. I think it is taking the first record (i.e. header row along with the first row as one, and since there are now more pipes ( | ) the error is thrown)

I've tried setting different values for these properties but to no avail... Any help will be deeply appreciated...

Thanks
Gogula

Bulk insert task expects the header and footer records also to be in the same format as other Data Rows, so to do, what you are trying to do, your data needs to be in this format:

HEDR | yadi | yadi | yada
500 | Data Data | More Data | 600
460 | Datum | More More | 705
550 | Data | Data | 603
FOOTR| yadi | yadi | yada

This is the same behavior as Transact-SQL bulk insert, and if you try something like the following with your file, you will encounter errors as well. Therefore, this is by design, according to what bulk insert supports.

BULK INSERT tempdb.dbo.Table_1

FROM 'C:\\File.txt'

WITH

(

FIELDTERMINATOR ='|',

ROWTERMINATOR ='\n',

firstrow = 2,

lastrow = 4

)

|||Hi Rangeeta,

Thanks for the reply. But what if in case there might be more than one header and footer (which I programmatic ally count and take into consideration) and those headers cannot be separated by the delimiters (since the client needs to send it to us)? It seems a little unfair that Bulk Insert expects the headers to be in the same format as the other rows...
Are there any alternate methods to do this?

Thanks again
Gogula