image load in azure table from azure blob

Question

Hi Azure server export,
Below is my Blob storgage URL
--https://artuat.blob.core.windows.net/report-logo/Pav_Red .jfif

my error run below code
Referenced external data source artuat.blob.core.windows.net not found.

I am fllowing beow code but i am facing error

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2010%ar$l';

CREATE DATABASE SCOPED CREDENTIAL Report
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SSRS%Logo@AZURE'

 CREATE TABLE [dbo].[SFMC_HTML_Images](
     [HTML_Image] varbinary(max) NULL,
     SendID varchar(50) null
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO

 Insert into SFMC_HTML_Images (HTML_Image) 
 Select BulkColumn FROM OPENROWSET( 
 BULK 'pav_Red .jfif', 
 DATA_SOURCE = 'https://artuat.blob.core.windows.net', SINGLE_BLOB) AS ImageFile

please any help on this

Thanks
Shanvitha

Answer 1

Hi Shanvitha,

It looks like you have only created a SQL credential, rather than an external data source. The SQL credential will work for things like backup and restore from URL, but it will not work for bulk inserts from blob.

You will need to also create an external data source before it can be referenced:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated

If you follow the above guide then that should work for you.

Please let me know if you face any further issues though.

Thanks,
Matt

Answer 2

MathewBradley ,

Thank your replay and your suggestion i have created Exteranl data source but still i am facing same error
my error run below code
Referenced external data source artuat.blob.core.windows.net not found.

below is my Code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2010%ar$l';

CREATE DATABASE SCOPED CREDENTIAL Report
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SSRS%Logo@AZURE'

/*My image locations :https://artuat.blob.core.windows.net/report-logo/
Here muliple image
'pav_Red.jfif' location is:https://artuat.blob.core.windows.net/report-logo/pav_Red.jfif
*/

CREATE EXTERNAL DATA SOURCE Report
WITH
( LOCATION = 'https://artuat.blob.core.windows.net/report-logo/' ,
CREDENTIAL = AVIS_Report ,
TYPE = BLOB_STORAGE
)

  CREATE TABLE [dbo].[SFMC_HTML_Images](
      [HTML_Image] varbinary(max) NULL,
      SendID varchar(50) null
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO

  Insert into SFMC_HTML_Images (HTML_Image) 
  Select BulkColumn FROM OPENROWSET( 
  BULK 'pav_Red.jfif', 
  DATA_SOURCE = 'https://artuat.blob.core.windows.net', SINGLE_BLOB) AS ImageFile

please can validate mycode i am new.

Answer 3

I guess, there is something wrong when you CREATE DB Scope Credentials.

The Secret (SAS token) should be very long string but you are using some short value - SSRS%Logo@AZURE

See the secret part in below document. This SAS has to be generated in Azure Blob storage account and then use it in your code

https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15#j-importing-into-a-table-from-a-file-stored-on-azure-blob-storage

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 4

HI VaibhavChowdary,

Thanks you replay I have genereated SSA key from Azure

I am facing Below error:
Cannot bulk load. The file "jfif" does not exist or you don't have file access rights.

My insert Statement is :
INSERT INTO SFMC_HTML_Images with (TABLOCK) ([HTML_Image], SendID)
SELECT * FROM OPENROWSET(
BULK 'jfif',
DATA_SOURCE = 'Report',
-- FORMAT ='jfif',
FORMATFILE='jfif',
FORMATFILE_DATA_SOURCE = 'Report'
) AS DataFile

My Steps:
My access blob access:

step2: shared access signature below option selected then i have generated SAS key
after ? delete and remaing added CREATE DATABASE SCOPED CREDENTIAL

please help

Thanks
Shanvitha