question

tomo-7428 avatar image
0 Votes"
tomo-7428 asked SumanthMarigowda-MSFT edited

I can't BULK INSERT a Blob Sotorage CSV file in Azure SQL Database.

Hello

I want to insert into Azure all at once, but I get the following error.


ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'AzureBulkInsert123';
GO

ALTER DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'se=2021-04-30T10:25:16Z&sip=192.168.0.226&spr=https&sv=2020-02-10&sr=b&sig=nvF6dpC02N%2FoLQBROvnKrhWieEyz388***';

DROP EXTERNAL DATA SOURCE MyAzureBlobStorage;

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://dataarea.blob.core.windows.net/bulk-ins-data'
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT dbo.{TABLE}
FROM 'test-00001.csv'
WITH(
DATA_SOURCE = 'MyAzureBlobStorage',
DATAFILETYPE = 'char',
FORMAT = 'CSV'
);


Cannot bulk load because the file "test-00001.csv" could not be opened. Operating system error code 5(Access is denied.).



Please tell me.

azure-sql-databaseazure-blob-storage
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

shivapatpi-MSFT avatar image
0 Votes"
shivapatpi-MSFT answered SumanthMarigowda-MSFT edited

Hello @tomo-7428 ,
Thanks for your query ! This error some times will be misleading more significantly when you use this "BULK INSERT".

Couple of mitigations mentioned here in the below article , kindly go through it.

Couple of additional settings which you might have to do in giving SQL Server access to that folder

some times , it can be due to remote connections using named pipes

Try to login with SQL Server Authentication instead of Windows Authentication

Last but not the least try to validate your SAS token so that it should not have any additional special characters



Let us know if the above article references solves your issue.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello shivapatpi-MSFT.
Thank you for the answer.

In the 1st link...
It has a file extension and makes the container readable.

SECRET = 'sp=r&st=2021-04-16T07:06:43Z&se=2021-04-29T15:06:43Z&sip=192.168.0.226&spr=https&sv=2020-02-10&sr=c&sig=NOtQS%2Fn0QFFgSuFS8xnsSxejlfFOgn1PhLlddeSjCYo%3D';

In the 2nd link...
I didn't know how to configure bulkadmin in Azure.
Are Azure defaults disabled?
Will it be a paid setting?

From the 3rd link...
It feels like you don't have permission to read the file.

I wanna realize bulk insert for cloud data migration in a short time.

It has a storage account / container / CSV file configuration.
I don't think there is a problem with the configuration.

Thank you.

0 Votes 0 ·

Hello.

Resolved the firewall settings for all networks.
It's a temporary connection, so it's okay.

Thank you very much.

0 Votes 0 ·