[dba-VB] VB.Net / SQl Server 2005 - Export file generator

jwcolby jwcolby at colbyconsulting.com
Fri Jun 22 17:10:03 CDT 2007

I need to export all of the records from my list tables out for address
validation, periodically.  Thus I need to be able to specify the source
table, the size (number of records) each file will contain, the number of
records exported etc.

In order to accomplish this I am building a VB.Net application to allow me
to export specific fields of a specific table into files of size X records.
All of the parameters are in tables that I create.  I have it working now,
and it is now dumping the name / address fields from 99 million records, out
to 1 million record files.  It is interesting to note (and the reason for
this email) that getting the data from SQL Server is the slowest part.  I am
building up a query dynamically (yea, I know I should be using a stored
procedure with parameters, that will follow) with the name of the table, the
field names to pull, and the start PKID and end PKIDs.  VB.Net is having
waitlock issues if the time to get the records is too large so I broke it
down to 100K record sets, then export 10 of those recordsets into a single
file.  In average, 100K records is taking about 20-25 seconds to return the
datasets, and then the VB.Net code that writes the file can write the data
out to the file in about 2 seconds.  So on average I am getting a million
record file built in about 100-120 seconds.  My program creates a unique
name for each file and dumps them in a directory specified in the an export
spec table.

This is the second piece of the big picture where a set of data files is
imported in to sql server, then immediately dumped back out for address
validation (this piece) then imported back in to SQL Server for use.  This
same piece will later be used to periodically dump the data tables that have
already been address validated back out to be validated again (NCOAs).  When
finished, this process will run automatically every week (or other time
period I specify) to dump every one of my tables out for a periodic address
(NCOA) validation.

Without automation this process is immensely time intensive, but with
automation it will be... Well... Automatic, with little or no manual
intervention.  So part two is complete, just a few tweaks left (logging
results and such).

John W. Colby
Colby Consulting

More information about the dba-VB mailing list