'azure sql db Cannot bulk load because the file ".xml" could not be opened. Operating system error code (null)

I am trying to insert xml file data into Azure SQL DB using stored procedure in github action. But getting error. Cannot bulk load because the file ".xml" could not be opened. Operating system error code (null).

Let me know what can i do to resolve this issue.

azure-sql-databasebulkinsert


Solution 1:[1]

BULK INSERT into Azure SQL DB can only work from Azure Storage Blob, but not from file systems on your local drive.

Here is the same error when I try from local source: enter image description here

Refer: Using BULK INSERT , an example showing how to use the BULK INSERT command to load data from a xml file in an Azure Blob storage location on which you have created a SAS key. The Azure Blob storage location is configured as an external data source. This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

Step1:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myazureblobstorage.blob.core.windows.net', CREDENTIAL= MyAzureBlobStorageCredential);

Step2: Place your file in blob container and access it like below

BULK INSERT Product
FROM 'product.csv'WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
        TABLOCK);

Refer: https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-bulk-load/load-from-azure-blob-storage/LoadFromAzureBlobStorage.sql

Solution 2:[2]

I couldn't get rid of the same issue but the Import Flat File Wizard has proven to be an amazing workaround as you can simply import a plain csv|txt file directly into your database.

First, create a table in your database into which you will import the CSV file. After the table is created: Log in to your database using SQL Server Management Studio.

  • Right-click the database and select Tasks -> Import Data...
  • Click the Next > button.
  • For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.
  • For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.
  • In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.
  • Check Run immediately and click the Next > button.
  • Click the Finish button to run the package.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 basquiatraphaeu

Related Questions