Azure Data Factory - help needed to ingest data, that uses an API, into a SQL Server instance

Hi D_PBI ,

Thank you for reaching out to the Microsoft Fabric Community Forum. 

When working with an API source in Azure Data Factory, the approach differs from loading data directly from a database, as ADF must first authenticate before accessing any data. This is why the vendor referenced using a client ID and client secret most APIs require you to generate an access token via an authentication endpoint. The Postman collection provided serves as a reference, outlining the necessary requests, headers, and authentication flow to replicate within ADF.

To configure this, begin by creating a pipeline and adding a Web activity to obtain the access token from the vendor’s token URL, typically using a POST request with the required client credentials. Once you receive the token, store it in a pipeline variable using a Set Variable activity. Next, use a Copy Data activity with the REST connector to call the actual API endpoint, passing the token dynamically in the Authorization header as a Bearer token. The resulting data can then be written directly into your Azure SQL table by setting up the sink and mapping the fields as needed.

Additionally, verify if the API response is paginated or contains nested JSON, as this may require further configuration to ensure complete data ingestion. With these steps, you establish a secure and efficient method for loading API data into Azure SQL using Azure Data Factory.

Thank you.