[AccessD] using a saved SSIS with VB.Net

Hi John

That looks right. I don't know what speed to expect other than it should be more than the typical a-couple-of-hundreds per second for inserts via DAO and ODBC. The only tricks I know of are the well-known to drop indexes from the receiving table during the import and - as you mention - to minimize logging.

The reference to MySQL is just for you to have an ultimate goal. I haven't seen or heard of anything else running at that speed, not even JET where you can expect around 15k records/s. Of course, it should server a purpose for you to install it, but MySQL is very easy to set up - and we have true experts (not me) joining the list. So if you can see an idea having these files loaded into an engine very fast, just move on.

On the other hand, if you have a night and can perform the import without manual intervention, I think I would choose the method you have described.

/gustav


>>> jwcolby at colbyconsulting.com 24-04-2007 14:11 >>>
Gustav,

I found a syntax for SQL Server that, executed directly, allows me to do the
import from inside of SQL Server.

SELECT * 
FROM
OPENROWSET( 'MSDASQL',
		'Driver=(Microsoft Text Driver (*.txt, *.csv));
		DEFAULTDIR=d:\YadaYada;Extensions=CSV;',
		'SELECT * FROM NAR_CA1N.CSV')

This syntax directly opens a dataset, which can then be fed into an INSERT
statement as the "data clause".

It works, I have done so.  I have no idea how fast it is, but not
particularly I suspect.  One thing I have to go do is look at the log files
and see if I need to turn off logging.  

Another thing I need to do is TURN OFF the windows updates, which rebooted
my machine in the middle of the night last night!!!  If I have no logging, I
certainly don't want Windows rebooting itself in the middle of an import.

>though probably not so fast as MySQL can load data (see my previous post):
50k records/s

What are you suggesting with this?  That I set up MySQL on this machine just
to do the imports and then import the data out of MySQL into SQL Server?  It
seems that with as many problems as I have getting up to speed on SQL Server
I am doubling my problems trying to install another entire database server
and learn it as well.  I understand that you know and love MySQL but I am
pretty certain I have enough problems already without adding MySQL to my
plate.


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 4:34 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Hi John

Having the csv files created, I see no reason to use Access to guide the
import to SQL Server. Either BCP, as Bobby mentions, or a bulk statement-
which you could adjust on the fly as a pass-through query - could be used at
much higher speed:

  http://msdn2.microsoft.com/en-us/library/ms187042.aspx 

though probably not so fast as MySQL can load data (see my previous post):
50k records/s

/gustav