none
Bulk Insert from storage account 'files' not 'blobs' RRS feed

  • Question

  • Hello

    I've successfully imported files using bulk insert where the file is in a 'blob' in a storage account. My question is can we bulk insert from 'files' in a storage account and does anyone have example scripts to configure and import a file?

    By configure I mean I had to run the following to configure blobs so they could be imported:

    CREATE MASTER KEY...

    CREATE DATABASE SCOPED CREDENTIAL...

    CREATE EXTERNAL DATA SOURCE...

    Thanks, Brian


    Brian Searle

    Wednesday, October 4, 2017 11:48 AM

All replies

  • Hello Brian,

    Yes you can use the bulk insert.

    Azure SQL Database will enable you to directly load files stored in Azure Blob storage by using the following SQL statements:

    ·        BULK INSERT T-SQL—command that will load a file from a Blob storage account into a SQL Database table
    ·        OPENROWSET table—value function that will parse a file stored in Blob storage and return the content of the file as a set of rows

    Also plz check out this link 

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by Kapil.Kumawat Wednesday, October 4, 2017 1:20 PM
    Wednesday, October 4, 2017 1:20 PM
  • Yes I agree, I know how to use bulk insert with Azure storage blob objects, I've done that.

    What I want to know is if it works with Azure storage files objects?


    Brian Searle

    Wednesday, October 4, 2017 1:53 PM
  • Yes, I think it will work.

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Wednesday, October 4, 2017 3:33 PM
  • Can you provide examples of the configuration scripts as I only know how to configure Azure SQL DB for blob objects?

    Brian Searle

    Wednesday, October 4, 2017 3:36 PM
  • This answer is appropriate for blob storage, but I have yet to see to work for file storage.

    Just like Brian, I have created a new DATABASE SCOPED CREDENTIAL, EXTERNAL DATABASE, then attempted to BULK INSERT.

    CREATE EXTERNAL DATA SOURCE MyAzureFileStorage
    WITH ( TYPE = BLOB_STORAGE,
           LOCATION = 'https://filestorage123.file.core.windows.net',
           CREDENTIAL= MyAzureFileStorageCredential)
    

    BULK INSERT ETL.BulkInsert
    FROM 'filesharename/data.csv'
    WITH (
           DATA_SOURCE = 'MyAzureFileStorage',
           FORMATFILE='filesharename/ImportFormat.fmt',
           FORMATFILE_DATA_SOURCE = 'MyAzureFileStorage',
           FIRSTROW = 2
    )
    

    After a long wait SSMS gives the following error

    Msg 4861, Level 16, State 1, Line 39
    Cannot bulk load because the file "filesharename/data.csv" could not be opened. Operating system error code 12150(failed to retrieve text for this error. Reason: 317).
    

    Maybe it wants a leading slash in the FROM clause?

    Msg 12704, Level 16, State 1, Line 39
    Bad or inaccessible location specified in external data source "MyAzureFileStorage".
    

    Maybe I should put the file store into the DATA SOURCE url?

    Msg 4861, Level 16, State 1, Line 6
    Cannot bulk load because the file "data.csv" could not be opened. Operating system error code 12150(failed to retrieve text for this error. Reason: 317).
    

    If you have any other ideas, or a working code snippet, I'd really appreciate it.

    Tuesday, November 14, 2017 11:33 PM
  • Hi, have you found the solution to bulk insert from file storage ?
    Thursday, July 18, 2019 1:40 PM
  • Alas no, conclusion is its not supported.

    Note bulk insert is only preview

    I aslo found constraints with it, it does not support working over a vnet connection between SQL and Storage account.

    Best use a different approach!


    Brian Searle

    Thursday, July 18, 2019 1:45 PM