Tuesday, July 14, 2009

Bulk insert a file with Header and Footer

I have a file like the following with Header and Footer.

HDR|20090713014036|4
1|Peter|Junkins|1/12/2005|Dallas|TX|75252
2|David|Junkins|10/12/1999|Dallas|TX|75080
3|John|Sexton|1/12/1990|Dallas|TX|75753
4|Jack|Bill|1/1/1990|Dallas|TX|75252
FTR

I tried to used bulk insert task in DTS using FirstRow and LastRow Attributes. But It is not working as I thought it would work.
1. If I use FirstRow=2 LastRow=5 it fails with the following error.
"Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
2. If I use FirsRow=2 LastRow=4 It works but Last data row is not loaded

Just for testing I tested with the following scenario. It worked.
3.change footer to FTR|||||| and FirstRow=2 and LastRow=5. It works.

Conclusions:
1. Bulk insert can be used only when all the rows have same number of fields/columns
2. Regular DTS with Transformations works fine. It ignores footer row.
3. Bulk Insert cares about the row after the LASTROW mentioned in the attributes. I don't why????????????????

No comments:

Post a Comment