Share via

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

Shaheer Ali 0 Reputation points
2023-11-15T07:28:31.27+00:00

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,914 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Carlos Solís Salazar 17,441 Reputation points MVP
    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:

    1. 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.
    2. 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.
    3. 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!