[AccessD] using a saved SSIS with VB.Net

Hi John

Yes, I hope you get paid well.

Had you installed your SQL Server 2005 in "backwards compatibility mode" or whatever it is called - to behave like an SQL Server 2000 - it is my understanding that you could have created an ADP which gives you direct access to create and edit stored procedures in the SQL Server engine.
I have never done this myself so someone else might guide you here what your options are.

/gustav

>>> jwcolby at colbyconsulting.com 24-04-2007 16:32 >>>
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
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 8:54 AM
To: accessd at databaseadvisors.com 
Subject: Re: [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