How to setup an ETL pipeline using DB that cannot be accessed via Azure Functions

Deepak 0 Reputation points
2024-09-26T23:04:58.5466667+00:00

I need to create a Python ETL pipeline to update a table used in a Power BI report, but I'm facing two issues.

  1. First, one of the ETL inputs is a self-hosted on-premises SQL Server database. I can connect to this SQL database within Azure Data Factory using a Linked Service with a specific Integration Runtime (not AutoResolveIntegrationRuntime), but I’m unable to connect to it using the pyodbc function in Azure Functions. My infrastructure team hasn't found a solution for this. Does anyone know how to connect to this database from Azure Functions? Just to note, I can connect to other SQL databases using AutoResolveIntegrationRuntime in Linked Services, and those work fine with Azure Functions.
  2. Secondly, if connecting to the on-premises database from Azure Functions isn’t feasible, what’s the best approach to load data from this SQL database using the Copy Data activity (or any other activity) in Azure Data Factory, and then pass that data to the Azure Function Activity so my Python code can process it?
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,953 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,643 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 32,181 Reputation points MVP
    2024-09-27T02:17:55.2+00:00

    Based on what I remember is it is possible to access an On Prem SQL database via Azure function but for that the Azure function should have been deployed under App service plan and you would have to set up hybrid network for the same.

    Also, you cannot paste data into Azure function as it is not a storage layer.

    So you can use Copy activity to copy data from On Prem SQL server into an Azure blob storage (as low cost) and read data from blob storage within Azure function

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.