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

Vijay Sawant 0 Reputation points
2024-10-21T14:22:23.4666667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,060 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,911 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,600 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Amira Bedhiafi 26,961 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')
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.