Migrating varbinary data from onprem SQL table to Azure blob storage.

Vijay Sawant 0

Hi,
I need to move the varbinary data in one of my on Prem SQL data table column
Column(binary_data) which needs to be uploaded to azure blob storage.
We have 2 million rows in the table and every row should create one file in the blob storage for the binary data stored in the column.(binary_data).
I am selecting only one column in the query so that it picks only the binary data.

When i use ADF with integration runtime.
Source as SQL Server
Sink : Azure blob storage --> Binary as the type

I get the error Source does not match the Sink.
If sink is binary then source should also be Binary.

Thanks

Vijay Sawnt

Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-10-25T22:08:00.6233333+00:00

Hi @Vijay Sawant

Please refer to the MS Q&A thread provided below, as it might offer some insights that could help you address your question.

Thread link: https://learn.microsoft.com/en-us/answers/questions/1685569/transfer-documents-from-sqlserver-in-varbinary-to

I hope this information helps. Please do let us know if you have any further queries.

Thank you.
Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-10-28T16:57:06.2866667+00:00

Hi @Vijay Sawant

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.
Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-10-29T17:39:37.7933333+00:00

Hi @Vijay Sawant

Just checking in to see on the last response. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.

1 answer

Amira Bedhiafi 26,491 Reputation points

2024-10-21T14:34:44.41+00:00
The source type (SQL VARBINARY) is not being handled as a binary data type in the pipeline. Explicitly casting the column to VARBINARY(MAX) and ensuring the sink is set to Binary should resolve the mismatch.

ADF expects the source and sink types to match when dealing with binary data. While SQL Server stores the VARBINARY type as binary, ADF may not automatically handle it as such during the transformation.

SELECT CAST(binary_data AS VARBINARY(MAX)) AS binary_data FROM YourTable

Then configure ADF Pipeline like below :

Source: Configure the source as your on-premises SQL Server using the Integration Runtime (IR). Make sure the dataset is set to read the VARBINARY column.

Sink: For the sink, configure Azure Blob Storage. Choose the Binary data format as the sink type. This will allow you to directly transfer binary files to Blob storage.

Mapping: Ensure that the source and sink mapping in ADF aligns the VARBINARY column from SQL Server to the Binary format in Azure Blob.

If you want each row of binary data to create a separate file in Azure Blob Storage, use a dynamic file naming mechanism in ADF.

In the sink configuration, set the file name dynamically using the row number, a unique identifier (such as a primary key), or a timestamp to ensure each row gets its own file.

concat('output_', toString(rowNumber()), '.bin')
Please sign in to rate this answer.
Vijay Sawant 0 Reputation points

2024-10-22T04:39:03+00:00

Make sure the dataset is set to read the VARBINARY column.
How to configure this on the source dataset?I could not find the setting any where mentioning it.

As soon as I select the sink type as Binary type from the azure blob storage
Mapping tab gets disabled, and I am not able to map the source binary column.

Vijay Sawant 0 Reputation points

2024-10-22T20:03:04.8133333+00:00

Amira Bedhiafi please can you help me based on my comments

Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-10-23T18:46:08.8233333+00:00

Hi @ Vijay Sawant

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

It seems like you are facing an issue while mapping the source binary column to the sink in Azure Blob Storage. As per the provided information, it looks like you have selected the sink type as Binary type, which is why the Mapping tab is disabled.

When you select the sink type as Binary, it means that you are copying the data as-is without any transformation. Therefore, there is no need to map the source binary column to the sink. If you want to perform any transformation on the data, you can select the sink type as DelimitedText or Avro, and then you can map the source binary column to the sink.

I hope this information helps, please do let us know if you have any further queries.

Thank you.

Vijay Sawant 0 Reputation points

2024-10-24T17:11:32.08+00:00

@Chandra Boorla
Thanks for the information.
I understand that I have selected the source as Binary that's why mapping is disabled.
My original problem is I have a varbinary column in the SQL table on prem and I want to migrate it to Azure blob.
Every row should create a blob object in the Azure blob storage.

And when select the data using query and try to map it to the blob storage it gives me error mentioning that source must be binary when sink is binary.

ADF is not considering the column data as binary not sure why.
When I preview the Source data i see it has read it as varbinary.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Migrating varbinary data from onprem SQL table to Azure blob storage.

1 answer

Your answer