[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

>>> jwcolby at colbyconsulting.com 24-04-2007 01:13 >>>
Well, it took about three hours to build an Access VBA system to turn the
fixed width file into a CSV with the leading / trailing spaces stripped off
of every field.  The code runs about 2.5 K records / second for the
conversion portion.  I name the CSV file with a fixed name and linked the
file to Access, so once that is done I run a simple append query that pulls
the data back out of the CSV and appends it into the SQL Server table.  

Unfortunately the append to SQL process is running at a little better than
500 records / second.  I have to figure that out or this will take weeks.

I have ~ 100 million records to convert so... 

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 JWColby
Sent: Monday, April 23, 2007 8:09 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Yea, a code "preprocessor" just seems to be the answer.  No one on these
lists have ever proposed a good (non code) solution for this particular
problem.  

The fixed width flat file just sucks as an import specification.  It
requires an external file to tell you the field names and field widths.
Even once you know that it leaves you with data in the fields with spaces
between the end of the data and the end of the field width, i.e. 'John' in a
first name field 25 spaces wide is actually 'John                     '.
Unless you strip off the spaces you end up with a HUGE database where
indexes are huge and matches with external data are difficult.

But it is what is used in the industry I am dealing with. 

Suck it up and move on I guess.

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: Monday, April 23, 2007 5:16 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Hi John

I think that wizards are mostly for one-time or simple repeated tasks.

However, do you remember my observation from last year:

<quote>
>> 2006-05-05 20:59
Subject: MySQL insert records

When inserting records by a pass-through query from Access, I've found that
reading the records off a text file using 

  LOAD DATA INFILE 
    '../data/test.txt'
  INTO TABLE 
    tempload

into an empty non-indexed table runs at about 50,000 records per second even
using very modest hardware.
This is way faster than any normal "Insert Into ... " I've tried.

However, it's a bit clumsy first to have to write the data to a file.
Any comments or suggestions?
</quote>

You should be able to do so if not from T-SQL then from Access, and then run
a modifiable import and append query in SQL Server which reads the data from
the MySQL table and appends them to the internal SQL Server table.
But - as you have already done the mechanics for converting the fixed length
records files to csv files - that route may be faster for you; at least it
is proven.

/gustav

>>> jwcolby at colbyconsulting.com 23-04-2007 05:56 >>>
I saved an import spec from the import wizard in SQL Server 2005.  I now
need to use that import spec in VB.Net, but I need to modify it.  I need to
change the source file, make it an append instead of a make table / append,
and I need to modify the datatype from NVarchar to varchar.  Of course I can
simply go back in to SQL Server and run the import wizard again, however
this is a fixed width file which means recreating all of the field name /
field width info a second time.
 
Is it possible to just do these mods on the existing import spec or should I
just bite the bullet and rebuild.  I have ~ sixty more files to import into
the table and I really don't want to do this 60 times.  The last time I did
this I actually built an Access app to create .csv files from the original
fixed width files.  It seems like the long way around the barn but then
again I do not see how this wizard is really very useful in this case.
 
Anyone?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com