Read varchar(max) field from sql server DB and export to datalake as actual document

Question

Read varchar(max) field from sql server DB and export to datalake as actual document

rajanisqldev-42 221

Hi,

I have a table in sql server with the below fields.

ActivityId, DocumentBody(images/documents), filename, MimeType(application/octet-stream)

I need to read this table and export to the blob container as an image/document depending on the filetype.

I am happy with ADF or Fabric Pipelines or any method.

Regards

3 answers

Your answer

Answer 1

Hi @rajanisqldev-42 You can handle this using either Azure Data Factory (ADF) or Microsoft Fabric Pipelines, by exporting the varchar(max) field (which should ideally contain Base64 or VARBINARY(MAX) data) to Azure Blob Storage or Data Lake as individual files.

Here’s an approach using ADF:

Linked Services: Set up connections to both your SQL Server and Blob Storage. If your SQL Server is on-premises, make sure to use a Self-hosted Integration Runtime.

Datasets:

Source: A SQL dataset that includes DocumentBody, Filename, and MimeType.
Sink: A binary dataset pointing to your target container in Blob or Data Lake.

Copy Activity:

Map the DocumentBody column as binary content.
Use the Filename column to dynamically name your files. For example:
```
  
  @concat(item().Filename, '.pdf')
```
(You can also adjust the extension based on the MimeType if needed.)

Note: If you have different file types (such as PDFs, images, etc.), you can use an If Condition or Switch activity to set the correct file extension or path based on the MimeType value.

Also, if the content is stored as Base64 rather than binary, you’ll need to decode it before writing it out. This can be done using either an Azure Function to convert Base64 to binary, or a Mapping Data Flow with a Derived Column for inline decoding.

Hope this helps. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

hi there rajanisqldev-42,

thanks for posting this, its a cool challenge )) for microsoft stack, u can use azure data factory or fabric pipelines to handle this. in ADF, set up a copy activity that reads the sql table. make sure u map the 'documentbody' column as a binary source. then, sink it into a blob container with dynamic filename based on the 'filename' column. ADF handles binary data pretty well, just check the 'binary copy' option in the sink settings. https://docs.microsoft.com/en-us/azure/data-factory/format-binary fabric pipelines can do this too, but ADF might be simpler if u're already in that ecosystem.

This might help in other tools too... if u're using python or something, u can read the varchar(max) as bytes and dump it directly to blob storage. Libraries like pyodbc for sql and azure_storage_blob for the upload part will work. just make sure u set the content type based on the 'mimetype' column when uploading ))

worth looking into the 'mimetype' field to ensure files open correctly. Sometimes 'application/octet-stream' is too generic, so u might wanna adjust it based on the actual file type.

aha, and dont forget to test with a small batch first, happy exporting :))))))))

rgds,

Alex

and "yes" if you would follow me at Q&A - personaly thx.
P.S. If my answer help to you, please Accept my answer
PPS That is my Answer and not a Comment

https://ctrlaltdel.blog/

Answer 3

rajanisqldev-42 221

Hi Venkat Reddy / Alex,

I am trying your suggestion in Fabric pipeline.

SELECT [Body], [FileName] FROM table as a source for Copy data activity.

How can I choose [Body] as binary and the properties for the sink?

I have set the Destination, format as binary. I amgetting error that the source should be binary.

Regards

Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-06-12T09:19:15.3066667+00:00
@rajanisqldev-42 The error you're getting usually means that the pipeline is expecting the source column ([Body]) to be binary, but it’s not recognizing it as such.

A couple of things to check:

If the [Body] column in your SQL table is actually of type VARBINARY(MAX), make sure your source dataset treats it as a binary column. In Fabric Pipelines, the dataset editor should let you mark that column as binary during schema mapping or dataset setup.

If it’s stored as a VARCHAR(MAX) and holds Base64-encoded content (which some systems do when storing files as text), then you can’t directly map it as binary. In that case, you’d need to decode the Base64 first — either using a Data Flow with a Derived Column or by calling an Azure Function that handles the conversion before writing it to Blob.

Since you've already set your sink to binary format, you're on the right track there. Also, don’t forget to dynamically name your output files using the FileName column something like:

@concat(item().FileName, '.pdf')

In the source dataset editor, when you're previewing or editing the schema, check if [Body] is mapped as a "Binary" data type if not, you might need to manually update the type or tweak the query to cast it as VARBINARY.

Hope this helps. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-06-13T09:37:09.32+00:00

@rajanisqldev-42 Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Read varchar(max) field from sql server DB and export to datalake as actual document

3 answers

Your answer