Azure Data Factory - copy binary data from API response into Azure SQL

Pratim Das, Partha C 346 Reputation points
2024-01-15T05:34:59.6166667+00:00

Hi, I'm facing problem in ADF while copying binary data into Azure SQL. Problem statement: I'm receiving a file without any extension as a response from an API. It is known, that file contains excel formatted data. I need to copy that data into Azure SQL using ADF. Is there any way to accomplish that? I was trying with 'Copy Activity'. But since data set is binary we can't provide mapping. Please help. Thanking you in advance. Regards, Partha

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,234 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2024-01-18T06:25:21.5033333+00:00

    Hi Pratim Das, Partha C , Thankyou for using Microsoft Q&A platform and thanks for posting your query here. As per my understanding you are trying to copy data from a file into Azure sql DB , however the challenge here is that the file is not having any extension.

    Sink Must be Binary when Source is a Binary dataset.

    You can't directly copy data from binary dataset to sql table, as When using Binary dataset in copy activity, you can only copy from Binary dataset to Binary dataset. You mentioned that the source is in excel format, so try to select excel as the source dataset format, even though the file doesn't have any extension and try to use sink as sql table with auto-create table option so that it would create the table schema according to the source schema. Hope it helps. Please let us know how it goes. If the answer helped you , kindly accept the answer by clicking on Accept answer button. Thankyou

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Amira Bedhiafi 28,381 Reputation points
    2024-01-15T12:41:55.9333333+00:00

    You didn't mention which API you are using so you many need to check the documentation to configure the HTTP dataset to receive the response. Since you can't directly insert binary data into Azure SQL, use Azure Blob Storage as an intermediary then you will be able to store the binary data in Blob Storage as a file (you mentioned it's Excel-formatted, so you might save it with an .xlsx extension). I am assuming also the following :

    • If the binary data is in Excel format and needs to be converted into a format suitable for Azure SQL (like CSV or TSV), you might need to perform a conversion step.
    • You can use Azure Functions or a Data Flow in ADF to convert the Excel file into a more suitable format for SQL storage. For the copy activity, configure the sink of the Copy Activity to your Azure SQL Database.
    0 comments No comments

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.