Need advice on a Data Import Routine

[Doug R]

Hello,

I have a system that I am writing to automaticly import Credit Transaction
data into a SQL Server 2000 Database. I am using a VB.Net application to
detect when the file arives and prep it for parsing. The file is
aproximately 10Mb of relatively complex hierarchal data that is defined by 2
character tokens at the begining of each data line. The structure breaks out
into aproximately 6 parent-child related tables with numerous lookup tables
on each primary table.

The previous aproach loaded each line of raw data into an Import table and
used a Stored Procedure to break down the information into the tables. The
Stored Procedure is large and very complex. It also has the problem that the
Parsing takes so long I need to implement a very long timeout on my
connection or I get errors. It is also difficult to track progress through
the Import for the user to see.

The options I see at this point are;

1. Use the current approach of loading the raw data into an Import table and
Parsing with a Stored Procedure.

2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data into
the apropriate tables.

3. Use a mixed aproach of breaking the massive Stored Procedure into smaller
procedures and managing the steps with VB.Net.

I am looking for any tips and advice that the guru's out there are willing
to give. Especially a comparision of the effiency of doing ADO.NET bulk
inserts vs. Stored Procedure parsing.

Thanks for any help given.
Doug

PS - Sorry for the cross posting in sqlserver.programming but I am hoping to
get a balanced set of views on this issue.