[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

>>> jwcolby at colbyconsulting.com 24-04-07 18:07 >>>
The CSV file is on the same machine.  It appears that the clause that pulls
the source table (csv file) into memory is taking a ton of time.  These are
large files, the smallest are a little under 200 million bytes and the
largest are up in the 3 gigabyte range.  It appears that SQL Server does not
read a few records and append them, but rather reads the whole CSV and then
starts appending all of the assembled records.

If I were a SQL Server pro I could probably speed this up considerably.
Alas, I am not.

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 Robert L. Stewart
Sent: Tuesday, April 24, 2007 11:53 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] using a saved SSIS with VB.Net

John,

One of the keys to getting it into SQL Server faster is to have the CSV file
on the server and not on a different machine. Network traffic can kill the
process and slow it down significantly.

Robert

At 10:22 AM 4/24/2007, you wrote:
>Date: Tue, 24 Apr 2007 10:32:44 -0400
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: Re: [AccessD] using a saved SSIS with VB.Net
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <003101c7867d$6cce93a0$657aa8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>Gustav,
>
>My bigger issue here is that there 56 of these files to import into SQL 
>Server, supposedly ~100 million records.  I have done about 8 million 
>records so far.  I really must get this thing automated such that it 
>just chunks through these CSV files without my having to be around to 
>start the next one.  I am working now on setting up the append query 
>using that syntax below into a stored procedure so that I can then just
replace the file name.
>After that I will need to write something in VB.Net or whatever to 
>execute the stored procedure feeding in all of the file names from a 
>specific directory, deleting the file once the stored procedure 
>finishes the import for a given file.
>
>I have never written a stored procedure.
>Obviously, given the above, I have never called a stored procedure from 
>code.
>
>So much to learn, so little time.
>
>Once this is imported I have to turn right around and export a subset 
>of fields from the table back out as 1 - 2 million record chunks for 
>CAS / DPV / NCOA processing, then I have to import THOSE back in to a new
table.
>
>And when this set of data is finished, I have another set of about the 
>same size on the way, to which I have to perform the same processes.  I 
>soooooo need to get this process automated.
>
>John W. Colby