Capturing Errors in bulk inserts + dynamic BIs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Capturing Errors in bulk inserts + dynamic BIs

Author

Topic

khenry
Starting Member

16 Posts

Posted - 2002-02-21 : 06:41:06

I have a bulk insert to add a data update (char,col: \t, row: \n)

This data update is unreliable and includes header and footer records that are different from the rest of the data.

It is also very large (2.3 million), so I want to use as large a batch size as possible.

On average there are at least 50 error records scatterd through the data.

I want to pick up the error row from a bulk insert and restart it from that point. But I can't use variables and sp_executesql is proving difficult.

My present solution looks like this:


CREATE PROCEDURE sp_DynamicBulkInsert 
	@TableName		sysname		,
	@DataLocation		varchar(255)	,
	@BATCHSIZE		varchar(10)	,
	@CODEPAGE		varchar(10)	,
	@DATAFILETYPE		varchar(10)	,
	@FIELDTERM		varchar(10)	,
	@FIRSTROW		varchar(10)	,
	@MAXERRORS		varchar(10)	,
	@ROWTERMINATOR		varchar(10)

AS
begin
	declare @sql 	nvarchar(3000)
	set 	@sql 	= 	N'BULK INSERT ' + 	@TableName 	+ N' 
				FROM ''' 	+ 	@DataLocation 	+ N'''
				WITH (
					BATCHSIZE	= 	' + 	@BATCHSIZE 	+ 	N'	,
					CODEPAGE	=	''' + 	@CODEPAGE 	+ 	N'''  	,
					DATAFILETYPE	=	''' + 	@DATAFILETYPE 	+ 	N'''	,
					FIELDTERMINATOR	=	''' + 	@FIELDTERM	+ 	N'''	,
					FIRSTROW	=	' + 	@FIRSTROW 	+ 	N'	,
					MAXERRORS	=	' +	@MAXERRORS 	+ 	N'	,
					ROWTERMINATOR	=	''' + 	@ROWTERMINATOR 	+	N'''	,
					TABLOCK		)' 
	
	exec sp_executesql @sql
end
GO

...and then call this recursivly based on each count. But this isn't working very well.

any ideas?

K e i t h H e n r y

Edited by - khenry on 02/21/2002 06:42:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-21 : 06:55:02

Have you tried increasing MAXERRORS to something like 100 or so? This should cover the maximum number of errors you expect to see in the file.

The other options I can think of, if that doesn't work, is to import the file into a holding table, without concern for the data format, and the DELETE the header/footer rows and INSERT the remaining into the final destination table.

The second option is to use DTS with a custom transform task. This will be much slower than a BULK INSERT, but if nothing else works you may need to resort to it.

You could also pre-process the file using VB, JavaScript, Perl, whatever language you know best, to strip out the offending rows and leave only the valid data. Regular expressions are available in most languages and are pretty powerful in matching and replacing patterns.

khenry
Starting Member

16 Posts

Posted - 2002-02-21 : 07:27:08

Thanks

, I was already using MAXERRORS = 200, but when run as part of sp_executesql it just exits on the first error (as follows):

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

This is the error I'd expect for a batchsize of 0 or MAXERRORS = 0. When I run the bulk insert in a batch it runs fine, but I need it to be dynamic (if possible).

I have this running as a DTS (it takes about 8 hours), when the bulk works it takes about 1 hour. I'd rather make the server do the work [:-)]

K e i t h H e n r y

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-21 : 07:39:09

Have you tried using EXEC instead of sp_executesql?

I noticed that you're passing varchar variables but including them in an nvarchar string, I doubt this is causing the problem but you never know, stranger things have happened.

khenry
Starting Member

16 Posts

Posted - 2002-02-21 : 12:38:50

Yes, happens with exec, sp_executesql or if I statically run the code.

Looking at it further I always get the error above, and I expect it to encounter it, but I don't want the batch to terminate when it occurs.

I want to pick up the error with @@error and process the last few records one by one.
Something like Using the sproc above:

 
declare @DataLocation varchar(255),@ErrorRow int

set @DataLocation = '\\Server\share\data.txt'

exec sp_DynamicBulkInsert 'Loadstage', @DataLocation, 1000, 'RAW', 'char', '\t', 2, 200, '\r'

if @@error <> 0 
	begin
		select @ErrorRow = count(*) from Loadstage
		print 'Error found at ' + cast(@ErrorRow as varchar(25))
		print 'Importing records 1 at a time'
		exec sp_DynamicBulkInsert 'Loadstage', @DataLocation, 1, 'RAW', 'char', '\t', @ErrorRow, 200, '\r'
	end

Any help much appreciated.

K e i t h H e n r y

khenry
Starting Member

16 Posts

Posted - 2002-03-04 : 08:18:56

I'm still having problems with this, (had to leave it for a while while I did other things)

As I understand it, the MAXERRORS property of the bulk insert should allow me to specify the number of error rows to ignor before giving up.

However this process if failing on the first error row encountered.

Can only certain type of errors be ignored? I mean obviously a disk/network failure could kill the whole process, but an extra tab in a row should just cause it to skip to the next \n

... which it doesn't

K e i t h H e n r y

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-04 : 09:31:57

I think you're going to have to look into some pre-processing cleanup to get rid of the header and footer rows from the file before you try to BULK INSERT it. Whenever I had errors like these it was almost always garbage characters that threw off the column or row delimiters. I imagine you can't get the provider of the file to change to a format that you can work with (ask them, don't assume they can't or won't).

The only relatively easy way you might get around the problem is to import the file into a staging table with a single varchar(8000) column, no indexes or constraints, and then DELETE the rows that you don't want. Once that's done, you can modify the CSV techniques in these articles to parse out the data and INSERT it into the final destination table:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

I wouldn't worry about the dynamic bit for the time being. Get it working first or, even better, push back hard on whoever is providing the file to clean it up or create a format you can use.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-04 : 09:59:50

Check out the following threads related to similar problems I was having...

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=8045

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=8402

============
The Dabbler!

khenry
Starting Member

16 Posts

Posted - 2002-03-06 : 06:31:35

Thanks, I'll check those out

K e i t h H e n r y

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources