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@.207.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.