This article is contributed. See the original author and article here.
Today, I worked on a service request when our customer wants to have an error file in case that any issue may happen running a bulk insert process in Azure SQL Database.
Unfortunately, every time that our customer execute the bulk insert process they faced the following error message:
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt” could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt.Error.Txt” could not be opened. Operating system error code 80(The file exists.).
I reproduced the issue and I found the reason why our customer faced this error:
First of all, let’s try to create the example code:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'
CREATE DATABASE SCOPED CREDENTIAL [JM_Scope] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-12-12&ss=b&srt=sco&sp=rwdlacx&se=2020-10-28T23:38:22Z&st=2020-09-29T14:38:22Z&spr=https&sig=24TJMAf1hOzE2DDVqw1QRs....'
CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource] WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myblobstorage.blob.core.windows.net/jmread', CREDENTIAL = [JM_Scope] );
CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource_Error] WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myblobstorage.blob.core.windows.net/jmread/error', CREDENTIAL = [JM_Scope] );
CREATE TABLE Table1 (ID INT)
BULK INSERT [dbo].[Table1]
FROM 'filetoread.txt'
WITH (DATA_SOURCE = 'JM_EXT_DSource'
, FIELDTERMINATOR = 't'
, ERRORFILE = 'error.Txt'
, ERRORFILE_DATA_SOURCE = 'JM_EXT_DSource_Error')
I modified the filetoread.txt with an incorrect values as follows in order to raise a conversion issue.
1
2
3
IK
The first time that I executed the process everything worked as expected and I got an error:
Msg 4864, Level 16, State 1, Line 19
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).
Completion time: 2020-10-01T00:42:10.9217117+02:00
As result of this issue I got two files:
- Error.Txt that contains the data lines that are causing the error.
- Error.Txt.Error.Txt that contains the number line and the error message with all details.
The second time that I executed the process I got another error message:
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt” could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt.Error.Txt” could not be opened. Operating system error code 80(The file exists.).
This error is caused because if the ErrorFile exist in the destination folder there is not possible to overwrite and you need to delete the file. You have this information in this doc : “The error file is created when the command is executed. An error occurs if the file already exists. Additionally, a control file that has the extension .ERROR.txt is created. This references each row in the error file and provides error diagnostics. As soon as the errors have been corrected, the data can be loaded.“
Enjoy!
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments