[AccessD] using a saved SSIS with VB.Net

Gustav Brock Gustav at cactus.dk
Mon Apr 23 04:15:50 CDT 2007


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 






More information about the AccessD mailing list