[AccessD] using a saved SSIS with VB.Net

Gustav,

Yesterday afternoon I started looking at something similar.  

First I tried to just write a sql statement for each record and execute that
against SQL Server.  That runs about 300 records / second.  The advantage it
has is that is "one step", i.e. there is no "convert to csv" and then
"import to SQL Server".  ATM the "import to SQL Server requires manual work
on my part so this is truly automated from start to finish.  I unzip the
files to a directory, then my (Access/VBA) program picks up all the files
one by one and processes them, automatically.  Unfortunately processing 100
million records at 300 / second turns into 92.5 hours.  Fortunately it is
totally automatic, barring some outside influence such as disk full, power
loss, mangled file etc.

I then tried to write a DAO recordset to a temp table inside of Access.
Working directly with the recordset I AddNew / iterate the fields filling in
the data pulled from the flat file line, Update, do it again.  This creates
the temp table records at a rate of about 1000 per second but the MDB gets
HUGE, remember that the source files are often multi-gigabyte monstrosities.
Then appending the entire table to SQL Server just takes forever.

So essentially I tried your suggestion:

>  Insert Into <table> Values ( <value1>, <value2>, .. <valueN> )

And it does work, it is automatic and it is painfully slow.  OTOH, I just
used a Dao database object and used db.execute to send the data to sql
server.

Do you think it would be faster using the ADO method?

I am now timing having a dao.db.execute append an entire linked CSV file
into SQL Server.

In the end, I think that I can go with any of these methods as long as it
does not require manual intervention.  I already have CSVs generated for the
majority of these flat files.  I can do the rest and then just use Access /
VBA to bang the CSV files in to SQL Server.  If it takes four days well...
It will be done by Monday.

I really want to switch to VB.Net for all of this bit twiddling.  I think I
will use VB.Net to do the part where I pull the name / address stuff back
out to CSVs for feeding to Accuzip.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, April 24, 2007 6:24 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Hi John

Your observations about the flow are probably true.

If we for a moment return to your reading and converting of these huge files
in Access, instead of rewriting these to csv files for later import into SQL
Server, why not collect the finished records to batches of:

  Insert Into <table> Values ( <value1>, <value2>, .. <valueN> )

with, say, 100 or 1000 lines and feed these directly to SQL Server?
This can be easily done with ADO and a Connection object and a Command
object where you use the Connection.Execute method to load the batch of
records directly into SQL Server. For you it should be a simple matter to
modify your Access app, which you already have build, to perform the task.

/gustav