question

JacobusWooning-0564 avatar image
0 Votes"
JacobusWooning-0564 asked Mike-Ubezzi commented

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

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
11917-firewall.png


Not only is the firewall open for SQL server it's also open for the Storage account:
12093-storagefirewall.png



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://docs.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

azure-sql-databaseazure-security-centerazure-migrateazure-firewall
firewall.png (9.9 KiB)
storagefirewall.png (21.5 KiB)
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.

Mike-Ubezzi avatar image
0 Votes"
Mike-Ubezzi answered

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.


12437-screenshot-117.png


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


Regards,
Mike



screenshot-117.png (122.3 KiB)
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.

JacobusWooning-0564 avatar image
0 Votes"
JacobusWooning-0564 answered Mike-Ubezzi commented

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
12647-sas.png



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


sas.png (87.9 KiB)
· 1
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.

Thank you for the additional detail, @JacobusWooning-0564. Here is some additional information pertaining to Shared Access Signatures (SAS) and how to use them to limit access: Grant limited access to Azure Storage resources using shared access signatures (SAS)


0 Votes 0 ·