02 June,2021 by Tom Collins
Question: I have a SQL Server Agent job which includes a BULK INSERT step , but is generating an error leaving step in a loading state.
BULK INSERT MY_REPORT FROM 'E:\myreport.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', ERRORFILE = 'E:\Temp\report.log' )
The Error Message
: 'Exception calling "ExecuteReader" with "0" argument(s): "Cannot bulk load because the file "E:\Temp\report.log" could not be opened. Operating system error code 80(The file exists.). Cannot bulk load because the file "E:\Temp\report.log.Error.Txt" could not be opened. Operating system error code 80(The file exists.)." '. Process Exit Code -1. The step failed
How can I fix?
Answer: This error is occuring as the Error file already exists - maybe from a previous failure(?). According to the MS documentation:
"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"
Option1 : Delete the file run again. Although if the errors still exist , you'll find yourself in the same place again
Option 2 : Fix the errors , delete the files and run the job again
Read more on BULK INSERT
How to use a BULK INSERT ERRORFILE
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |