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

Jacobus Wooning 41 Reputation points
2020-07-14T08:00:31.357+00:00

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

Azure SQL Database
Azure Firewall
Azure Firewall
An Azure network security service that is used to protect Azure Virtual Network resources.
564 questions
Azure Migrate
Azure Migrate
A central hub of Azure cloud migration services and tools to discover, assess, and migrate workloads to the cloud.
717 questions
Microsoft Defender for Cloud
Microsoft Defender for Cloud
An Azure service that provides threat protection for workloads running in Azure, on-premises, and in other clouds. Previously known as Azure Security Center and Azure Defender.
1,188 questions
0 comments
Accepted answer
  1. Mike Ubezzi 2,776 Reputation points
    2020-07-15T16:01:13.95+00:00

    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

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jacobus Wooning 41 Reputation points
    2020-07-16T10:49:12.737+00:00

    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