Error when running a RDS stored procedure for PostgreSql from AWS in Azure Synapse Pipelines

Question

I have created a pipeline to run RDS stored procedure for PostgreSQL from AWS on Azure Synapse Pipelines. To execute a procedure I am using an activity lookup. In the Lookup configuration I am doing a dynamic query, where I make the procedure call which has 3 parameters. I created a variable and 2 parameters in the pipeline to dynamically insert the data in the query inside the Lookup.

But when I execute the pipeline it generates the following error.

ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the 'columns' property is invalid: 'The value cannot be null.\nParameter name: columns'.,Source=,' 'Type=System.ArgumentNullException,Message=The value cannot be null.\nParameter name: columns,Source=Microsoft.DataTransfer.Common

The curious thing is that taking the Lookup input and running it manually in postgres gives no error. And despite the error in the pipeline, a procedure is executed successfully in Postgres.

Answer 1

Lookup activity reads the data from the query provided as input.

Message=The value of the 'columns' property is invalid: 'The value cannot be null.\nParameter name: columns'.

This error indicates that the query is not returning any value. To solve this,

• you can modify the stored procedure to return any value.
• Otherwise, add a select statement after the call sp_name(); statement.

if your query looks like this,

call  sp_name(@param1, @param2, @param3);

modifiy it to look like this.

call  sp_name(@param1, @param2, @param3); 
SELECT 1;