Requesting SqlBulkCopy Advice

[myusenetaccount]

Hello all,
I'm looking for a bit of SqlBulkCopy advice for a web application that
appends large amounts of data into an existing SQL Server table.
SqlBulkCopy seems to be an excellent API for the task and it would
replace existing code that basicly iterated over a DataTable, calling a
SQL insert statement for each row, which I assume is a great deal
slower.

The problem I'm having is dealing with SqlBulkCopy errors that arise
when data type conversion fails or string truncation occures. I would
like the operation to continue and be notified of the problem rows so I
can programicly deal with them. For example, additional code would
replace values that failed data type conversion with BDNull.Values,
strings would be truncated and the insert would be manually preformed.

One of my thoughts was to set the batch size to say 1,000 and if a
batch fails, iternate over that section of the DataTable and manually
insert those rows. On the notify event I would remove the number of
rows equal to the batch size from the head of the DataTable this way
after an error occures I could resume a SqlBulkCopy operation at the
point the error occured. I suppose the effectiveness of this approach
is dependent upon the amount of bad data in the DataTable and guaging
the proper batch size.

My question is how sound is this approach and does anyone have
suggestions or alternative ideas?

Thank you,
- Harry

 

[Cowboy \(Gregory A. Beamer\)]

If you are wrapping the command line in a Process object, you can specifiy
the number of acceptable errors. Note that this will still fail hard when
the type is incorrect, however. Loading the data to a temp table that is all
varchar and massaging out bad data is an option, of course.

You can also use command line UNIX type sort tools, as some will weed out
bad records as they sort. You cna then bulk load the good records and do
your magic in SQL. This is less expensive, time wise, than some other
options.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************