Execute PostgreSql stored procedure in azure data factory

1
votes

I have a requirement to execute Azure PostgreSQL stored procedure using Azure Data Factory. Is there any way to do the same. I found that Stored Procedure activity only supports SQL procedures.

Basically I have some staging tables in PostgreSQL and I want to load data into target tables(in PostgreSQL). I have written some stored procedures to apply transformations and load data. I want to run those stored procedures in ADF. Is there any other suggested option to achieve this. TIA!

2
postgresqlazure-data-factory
What do you mean "only supports SQL procedures"? Only procedures with language sql but not procedures using language plpgsql? That's seems rather strange why would that make a difference as you call both types in exactly the same way. - a_horse_with_no_name
As you've discovered, the StoredProcedure activity only supports connecting (LinkedService) to SQL Server variants, and so does not support PostgreSql. You could try an Azure Function to delegate the call to PostgreSQL, that would be easily consumed in your pipeline. - Joel Cochran
Can you try adding a script in COPY activity wherein you execute the stored procedure. Ex: EXEC <Procedure_Name> - Pratik Somaiya
Thanks Pratik Somaiya. I am currently using that way but was searching if there is some other better way. I have to load around 700 tables, so I am creating a dynamic pipeline and cannot give separate script for each table in copy activity. Thus, researching if there is some other option too. - Sonam

2 Answers

0
votes

Yes, Stored Procedure activity doesn't support Azure PostgreSQL stored procedure. It only supports Azure SQL Database, Azure Synapse Analytics and SQL Server Database. So you can try to delegate the call to PostgreSQL with Azure Function as Joel said.

0
votes

There is a way to execute the PostgreSQL function from Azure Synapse Analytics or Azure data factory. use lookup activity with query as "select function-name()"