[AccessD] using a saved SSIS with VB.Net

Hi John

I'm not sure you may get much faster than about 1000 records/s with ADO.
Here's what I have tried:

Public Function AppendBatchADO(ByVal lngRows As Long)

' Inserts about 1000 records/s.

  Dim cnn     As ADODB.Connection
  Dim cmd     As ADODB.Command
  
  Dim lngRow  As Long
  Dim strSQL  As String
   
  Set cnn = New ADODB.Connection
  Set cmd = New ADODB.Command
  cnn.ConnectionString = "DSN=Test;DATABASE=TestDb;"

  cnn.Open , "SA", "password"
  cnn.CursorLocation = adUseServer

    For lngRow = 1 To lngRows
      strSQL = "Insert Into dbo.Sample Values ( " & _
        CStr(lngRow) & "," & _
        "'Name'," & _
        "'Address'," & _
        CStr(1) & ")"
      cmd.CommandText = strSQL
      cnn.Execute cmd.CommandText, , adCmdText + adExecuteNoRecords
    Next
  cnn.Close
  
  Set cmd = Nothing
  Set cnn = Nothing
  
End Function

The advantage would be, that you can handle whatever size of input file, read one line, convert it, and insert it. Done. Next line.
You could probably set up several instances to handle several files simultaneously.

/gustav

>>> jwcolby at colbyconsulting.com 25-04-2007 14:06 >>>
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