Thursday, 15 December 2022

Cannot bulk load because the file could not be opened. Operating System Error Code 5

Azure sql  Bulk insert error 

Issue - 

Cannot bulk load because the file could not be opened. Operating System Error Code 5


Analysis
I followed below steps to do Bulk load but was getting above error

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test1223$';

CREATE DATABASE SCOPED CREDENTIAL blobcred1
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D'

CREATE EXTERNAL DATA SOURCE CustomerDS4
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://loyaltyhyperscale.blob.core.windows.net/loyalty',
        CREDENTIAL = blobcred1
    );


BULK INSERT [dbo].[CustomerTable] FROM 
'Customer.csv' 
WITH (DATA_SOURCE = 'CustomerDS4', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')


Resolution - To resolve the issue remove the ? in SAS token and Bingo...issue resolved

i,e use sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D

instead of 
?sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D

No comments:

Post a Comment