none
unable to do bulk insert from azure blob(.csv file ) to azure sql database RRS feed

  • Question

  • Task: We are trying to load the .csv file from blob storage to azure SQL by using bulk insert command.
    To perform the bulk insert we do the below prerequisites and executed the bulk insert command. But we are getting file access error. Please help me out on this.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc@123';

    GO
    CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sv=**************8';

    CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
    WITH ( TYPE = BLOB_STORAGE,
              LOCATION = 'https://**********.blob.core.windows.net/container_name'
              , CREDENTIAL= MyAzureBlobStorageCredential
    );
    INSERT INTO testing with (TABLOCK) (name,city)
    SELECT * FROM OPENROWSET(
       BULK  'test.csv',
       DATA_SOURCE = 'MyAzureBlobStorage',
       FORMAT ='csv',
       FORMATFILE='Test.csv',
       FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
        ) AS DataFile;

    Error msg:

    Msg 4860, Level 16, State 3, Line 12
    Cannot bulk load. The file "Test.csv" does not exist or you don't have file access rights.

    Alternatively we  tried other options :

    1.created VM on azure and installed sql server

    2.executed above command while executing below command 

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

    )

    we are getting error incorrect  syntax near external

    we are curious to know  is bulk insert will work on azure environment (Azure blob .csv to Azue SQL) because we tried multiple options as mentioned above.

    your help in this regard will very appreciated

    Thanks,

    Ruchika. 

    Tuesday, November 26, 2019 12:29 PM

All replies