0

We are working with Azure Data Factory and there is this pipeline in which I am trying to check the response of an API which reports a pipeline status. If it returns 'InProgress', I want to wait for some time before doing the same thing until the status is either 'Succeeded' or 'Failed'. I can easily achieve this in ADF but we are being asked to reduce the pipeline cost to as low as possible. I have done some research and found that implementing a wait functionality in SP compared to the Wait-Until construct in ADF is significantly cheaper (Stored Procedure activity being an external activity). And thus, I am interested to see how to achieve this in stored proc.

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • 1
    can you share what you have tried – Pratik Lad Jan 23 '23 at 06:04
  • I was going through this - https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure To be more specific, I want to understand if somebody has been able to do it in Azure SQL DB. – SouravA Jan 23 '23 at 06:07

1 Answers1

1

You can leverage Azure SQL Database External REST Endpoints Integration https://datasharkx.wordpress.com/2022/12/02/event-trigger-azure-data-factory-synapse-pipeline-via-azure-sql-database/ wherein directly access the API via Azure SQL database within an SP.

Sample blog for some other use case: https://datasharkx.wordpress.com/2022/12/02/event-trigger-azure-data-factory-synapse-pipeline-via-azure-sql-database/

Nandan
  • 3,939
  • 2
  • 8
  • 21
  • Thanks for replying. I would prefer not setting up an Azure Function. Interested to know why you went with that approach and not a bare call of API from proc. – SouravA Jan 23 '23 at 06:24
  • 1
    The API call would need some authorization which would be done via Access token and Access Token has a life time of only 90 mins, so preserving it within a database scoped credential wont make much of a difference. So we would need to generate it at run time. but in case if your wish is to trigger the proc via ADF, you can pass the token to the proc via ADF – Nandan Jan 23 '23 at 06:50