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')