I want to transfer the image data from the SQL table to a blob storage.

Shaheer Ali 0

I want to transfer the image data from the SQL table to a blob storage. The data is currently stored in a varbinary format. However, when attempting to set up the pipeline and define the source and destination, I encountered a validation issue stating that "Source must be binary when sink is binary dataset." Despite the data type in the table already being varbinary, I'm unsure how to address this. Are there any alternative methods to achieve this migration, considering that the varbinary data needs to be migrated from SQL to the blob storage as an image?

1 answer

Carlos Solís Salazar 17,971 Reputation points

2023-11-15T12:06:50.6+00:00
Transferring image data from a SQL table to Azure Blob Storage, especially when it's in varbinary format, can be a bit tricky, but it's certainly doable. The error message you're encountering, "Source must be binary when sink is binary dataset," suggests a mismatch in the configuration of your data pipeline. Let's go through the steps to address this:

Correct Pipeline Configuration: Ensure that your Azure Data Factory pipeline is correctly configured. The source dataset should be set to your SQL database with the correct table and column specified. The sink (destination) dataset should be set to your Blob Storage. The key here is to ensure that the pipeline understands that it's dealing with binary data at both ends.

Binary Dataset Configuration:

In your Source Dataset, make sure that the column containing the varbinary data is correctly identified and that its type is set to Binary.

In your Sink Dataset (Azure Blob Storage), you should also configure the dataset to expect binary data. This might involve setting the dataset's type to Binary or specifying the correct file format (like .jpg or .png) if your images are in a specific format.

Data Flow Transformation (if necessary):

If direct transfer without transformation is not working, consider using a Data Flow in Azure Data Factory.

You can read the varbinary data, and then use a Derived Column transformation to ensure it's in the correct format before writing it to Blob Storage.

For more details, follow the tutorial Copy data from a SQL Server database to Azure Blob storage

Hope this helps!
Please sign in to rate this answer.
Shaheer Ali 0 Reputation points

2023-11-15T13:34:16.3133333+00:00

Hey @Carlos Solís Salazar , thanks for the quick response, I really appreciate it. I tried the pipeline method with the correct source dataset. I selected the query option to retrieve only the Image Data column, and I set the sink dataset to my Blob Storage. However, I'm still encountering the same issue. When migrating binary data, Azure Data Factory isn't providing the mapping option, and I can't find any option for the dataset type to be Binary in the sink dataset. I need to explore the Data Flow Transformation option. I tried the method, but I couldn't find the derived column transformation logic. I'll check and update you here if it works. Thanks for the support.

Carlos Solís Salazar 17,971 Reputation points

2023-11-17T11:24:11.56+00:00

Finding Derived Column Transformation in Data Flow:

Access Data Flow:

In Azure Data Factory, navigate to the 'Author' section.

Here, either create a new Data Flow or edit an existing one.

Add a Transformation Step:

In the Data Flow workspace, you'll have a graph representing your data flow.

Select the plus icon (+) next to your source or any existing transformation to add a new transformation.

Select Derived Column:

From the list of transformations, choose 'Derived Column'.

This will add a Derived Column transformation to your data flow pipeline.

Configuring Derived Column Transformation:

Open Transformation Settings:

Click on the Derived Column transformation you just added to open its settings.

Define New or Modify Columns:

Here, you can define new columns or modify existing ones using expressions.

Use the expression builder to write expressions that transform your data. For example, if you're handling binary data, you might use expressions to convert or format this data appropriately.

Use Expression Language:

Azure Data Factory's expression language allows you to perform a variety of operations on your data. This includes string functions, type conversions, and conditional logic.

Preview and Debug:

Utilize the preview feature to see how your expressions change the data. This is useful for debugging and ensuring your transformations are correct.

Example Use Case for Varbinary Data:

If your source data is in varbinary format and you need to transform or prepare it for loading into Azure Blob Storage, you might write expressions in the Derived Column Transformation to convert this data into a suitable format for storage.

Documentation and Resources:

For detailed guidance and examples of using Derived Column Transformation, refer to the official Azure Data Factory documentation.

Accept the answer if the information helped you. This will help us and others in the community as well.

Shaheer Ali 0 Reputation points

2023-11-20T07:20:48.4866667+00:00

Hey @Carlos Solís Salazar , thanks for the response here,
I previously attempted a similar process to what you described, using a Data Flow with a Derived Column transformation to handle the data conversion. However, after completing the Data Flow, I encountered an issue when trying to add a Sink to output the data. The binary data type option was disabled by default in Azure and I was unable to select it for the Sink. I checked with the support team and they suggested handling the conversion in C# code, but this is not feasible for me given the large data volumes involved. C# would not be able to handle the load. I explored other ADF options but could not find a proper way to convert the varbinary data to image format within Azure.

Reema DSouza 40 Reputation points

2024-05-22T13:18:18.14+00:00

@Shaheer Ali Any luck solving this?

@Carlos Solis Salazar Azure doesn't let us select the Binary option at sink.

Data type is set to binary instead of varbinary in Azure. Should that be causing an issue?
Sign in to comment

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

Share via

I want to transfer the image data from the SQL table to a blob storage.

1 answer

Your answer