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

Question

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

TONY HERMESOM TAVARES GOMES 0

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.

VasimTamboli 5,215

Based on the error message you provided, it seems like the issue is with the columns parameter in the Lookup activity configuration. The error message states that the value of the columns property is invalid, and that it cannot be null.

Here are a few things you can try to resolve the issue:

    Check the Lookup activity configuration: Make sure that the columns property is set correctly and that it is not null. You can also try removing the columns property from the Lookup activity configuration to see if that resolves the issue.

    Check the pipeline variable and parameters: Make sure that the pipeline variable and parameters you are using in the dynamic query are set correctly and have the correct data type. You can also try hardcoding the values in the query to see if that resolves the issue.

    Check the data types in the Postgres procedure: Make sure that the data types of the parameters in the Postgres procedure match the data types of the parameters in the pipeline. If there is a mismatch, it could cause the Lookup activity to fail.

    Check the version compatibility: Ensure that the version of PostgresSQL supported in Azure Synapse Pipelines is compatible with the version of PostgresSQL in RDS.

If none of these solutions work, you can try reaching out to Azure Synapse Pipelines support for further assistance. They can help you troubleshoot the issue and provide additional guidance.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-05-23T11:10:06.38+00:00

Hi MOHAMMED, MUSHTAQ SIDDIQUE, Just checking if below answer helps. If yes, Please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries.

1 answer

Your answer

VasimTamboli 5,215 Reputation points

2023-05-02T18:15:18.9866667+00:00

Based on the error message you provided, it seems like the issue is with the columns parameter in the Lookup activity configuration. The error message states that the value of the columns property is invalid, and that it cannot be null. Here are a few things you can try to resolve the issue: Check the Lookup activity configuration: Make sure that the columns property is set correctly and that it is not null. You can also try removing the columns property from the Lookup activity configuration to see if that resolves the issue. Check the pipeline variable and parameters: Make sure that the pipeline variable and parameters you are using in the dynamic query are set correctly and have the correct data type. You can also try hardcoding the values in the query to see if that resolves the issue. Check the data types in the Postgres procedure: Make sure that the data types of the parameters in the Postgres procedure match the data types of the parameters in the pipeline. If there is a mismatch, it could cause the Lookup activity to fail. Check the version compatibility: Ensure that the version of PostgresSQL supported in Azure Synapse Pipelines is compatible with the version of PostgresSQL in RDS. If none of these solutions work, you can try reaching out to Azure Synapse Pipelines support for further assistance. They can help you troubleshoot the issue and provide additional guidance.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-05-23T11:10:06.38+00:00

Hi MOHAMMED, MUSHTAQ SIDDIQUE, Just checking if below answer helps. If yes, Please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries.

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi TONY HERMESOM TAVARES GOMES,

Thank you for posting query in Microsoft Q&A Platform.

It seems you might not configured your lookup activity properly, hence ending up with this error.

Kindly make sure not to declare your parameters in the script. Directly use those parameters in the script. And under script parameters field declare your parameters and pass values into them.

If above not helps. Kindly share your lookup activity screenshots to understand better and try to repro at my end to help better.

Please let me know how it goes.

TONY HERMESOM TAVARES GOMES 0 Reputation points

2023-05-03T13:59:09.6533333+00:00

Hi ShaikMaheer-MSFT,

Below is the printout of the query that I wrote in add dynamic content.

Importantly, the Lookup entry returns exactly the execution of the procedure. As a test I copied the Lookup entry and ran it directly in Postgres. It ran successfully.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-05-08T14:58:56.0333333+00:00

Hi MOHAMMED, MUSHTAQ SIDDIQUE, Here you are using some single quotes. Try to keep your single quotes in to a variable and then use that variable in the expression and see if that helps. Please let me know how it goes.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-05-17T16:13:41.2066667+00:00

Hi MOHAMMED, MUSHTAQ SIDDIQUE, Just checking if above answer helps. If yes, Please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries.

Share via

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

1 answer

Your answer