Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.2
07.46.248.16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.