[AccessD] using a saved SSIS with VB.Net

Bobby Heid bheid at sc.rr.com
Tue Apr 24 19:47:13 CDT 2007


John, 

Did you ever check out the BCP utility in SQL Server to import the files?

Bobby 

-----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

>>> 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





More information about the AccessD mailing list