How to insert image file, stored in Azure blob storage, into an Azure SQL table

I've set up an Azure blob storage and have uploaded one jpg file into the container.

In SSMS I've created all the external data source and credentials, master key etc.

Now I'm trying to use the BULK insert command to put the image into a table. Essentially this table will store images of HTML previews of emails that have been sent. They will need to have a corresponding ID.

This table will then be fed into powerBI and I'll do base64 encoding in that program to display the image. (found this method: https://community.powerbi.com/t5/Desktop/How-to-use-Images-Stored-in-a-SQL-Server-Table-with-Power-BI/td-p/383111)

However, when trying to run the bulk insert command I get this error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (HTML_Image).

I tried using a varchax(max) column type and it allowed it to go but in a weird format. (when you open an image in notepad - that's what it looks like)

Can anyone recommend a solution? Also, am I even using the correct method here as I'm not sure how to insert the ID value with this BULK insert command along with the image?

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


BULK INSERT SFMC_HTML_Images
FROM '4017033.jpg'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Solution

I have found the right method. For some reason, the difference between normal SQL and Azure SQL methods were confusing me. Everywhere I searched was saying Azure doesn't support OPENROWSET. But I tried this method and it worked:

Insert into SFMC_HTML_Images (HTML_Image) 
Select BulkColumn FROM OPENROWSET( 
BULK '4017033.jpg', 
DATA_SOURCE = 'MyAzureBlobStorage', SINGLE_BLOB) AS ImageFile;

However, the length of the image data is 170,000+ and PowerBI limits to 32k :( so the image is cropped.

I've since, (All in one morning) found that PowerBI has its own Azure Blob Storage connector! So all I had to do was connect to that and pull through the image via a custom visual.