Cannot bulk load because the file 'container' + file' could not be opened. Operating system error code (null).

Question

I am not sure what is going on with this quation body it keeps nagging that there need to be at least 10 characters inside this body.....

Loading a file from the Azure Blob storage should be relatively easy when you are working with a Azure SQL database right ?

Especially when the firewall is open right ?
Below the firewall settings for the Azure SQL Server

Not only is the firewall open for SQL server it's also open for the Storage account:

So when you wanna open the file in the storage account with openrowset then you would presume it would be something like:

SELECT *
FROM OPENROWSET(BULK 'http://storage..../container/folder/filename',
TYPE = 'PARQUET') AS file

seeing you 'must' use a datasource where the url is present it would be something like below:

SELECT *
FROM OPENROWSET(BULK '/folder/filename',
DATA_SOURCE='storage', --> Root URL is in LOCATION of DATA SOURCE
TYPE = 'PARQUET') AS file

According to the website:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

you be done now because it's very simple right?

WHY am i getting: Cannot bulk load because the file 'container' + file' could not be opened. Operating system error code (null).

if i wanna open the file through a web browser it's no problem

Answer 1

Hi @JacobusWooning-0564 - Are you working with Azure SQL Database or are you working with Azure Synapse Analytics with the SQL on-demand (preview) feature?

The OPENROWSET function is available in both SQL engines but behaves a little differently when it comes to creating the DATASOURCE and FORMAT values and supporting property values based upon the specific data source being used (see table). In the case of Azure SQL Database, please see: Using OPENROWSET with the BULK Option

If you are working with the Azure Synapse Analytics and the SQL on-demand, please specifically look at the storage account to ensure it is compatible with the protocol. OPENROWSET is a SQL Server level feature and is the same function in the case of Azure Synapse Analytics and Azure SQL Database but the DATASORCE requires the correct mix of parameters based upon the storage account and FORMAT being passed.

And yes, you will need to Allow Azure access on both the SQL Server and on the storage account, along with any SAS token required, based upon permissions set on the container. In rare cases you will need to explicitly set the IP address of the storage account in the firewall for the SQL Server instance.

Please let me know if you still have outstanding questions or issue with this deployment.

Regards,
Mike

Answer 2

Hi @Zagato36

First of all thank you very much for your reaction :)

The things you are describing are all true ...

In answer to your question...

i am only working with a serverless Azure SQL together with a storage account with container

For the openrowset it's nessesary to have SAS or another security but i have choosen SAS.

The SAS has to be generated through the storage account and that is what you need for the datasource.

But when you create the SAS

I only selected container instead of object...

how silly of me...

Then when you want to read the file you have no access...

And basically that is correct...

Cheers
Sjaak