Not able to execute Snowflake stored procedure from ADF via Script or Lookup activity

Question

I am not able execute Snowflake Stored Procedure which accepts parameters from ADF via Script or Lookup activity. Though I know Script parameters are not supported in Script activity currently after update but also as suggestion, I have tried to use a concat function to call the stored proc as below :

@concat('CALL EMPLOYEE_DB.DEV.GetEmpDetails(','''A''',')')

But it gives weird error as :
Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Execution.ScriptActivityExecutor,''Type=Apache.Arrow.Adbc.AdbcException,Message=[Snowflake] parsing time "18284" as "2006-01-02": cannot parse "4" as "-",Source=Apache.Arrow.Adbc,''

This is really frustrating that after the updates, parameters are not supported. Please help with a solution

Answer 1

Finally was able to figure it out with the updated/new connector.

Snowflake Stored Proc : [DB NAME : Emp_DB | Schema Name : Emp_Schema]

CREATE OR REPLACE PROCEDURE Emp_DB.Emp_Schema.UPDATEEMPDETAILS("ID" NUMBER(38,0), "EMAIL_ID" VARCHAR(16777216))

RETURNS VARCHAR(16777216)

LANGUAGE SQL

EXECUTE AS OWNER

AS 'BEGIN

UPDATE Emp_DB.Emp_Schema.Employee SET Email_ID= :Email_ID WHERE ID = :ID;

END';

Below code snippet can be used to run a stored proc from ADF via script activity :

declare  
query text := '';
begin
EXECUTE IMMEDIATE 'USE SCHEMA METADATA';
 
query := (Call UpdateEmpDetails(@{pipeline().parameters.ParamValue3_ID},'@{pipeline().parameters.ParamValue4_EmailID}'));
end;

Answer 2

Hello,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue: Unable to execute Snowflake stored procedure from ADF via Script or Lookup activity

Unable to execute Snowflake Stored Procedure which accepts parameters from ADF via Script or Lookup activity. Though I know Script parameters are not supported in Script activity currently after update but also as suggestion, I have tried to use a concat function to call the stored proc as below :

@concat('CALL EMPLOYEE_DB.DEV.GetEmpDetails(','''A''',')')

Error:

Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Execution.ScriptActivityExecutor,''Type=Apache.Arrow.Adbc.AdbcException,Message=[Snowflake] parsing time "18284" as "2006-01-02": cannot parse "4" as "-",Source=Apache.Arrow.Adbc,''

Solution:

Here is the solution with the updated/new connector.

Snowflake Stored Proc : [DB NAME : Emp_DB | Schema Name : Emp_Schema]

CREATE OR REPLACE PROCEDURE Emp_DB.Emp_Schema.UPDATEEMPDETAILS("ID" NUMBER(38,0), "EMAIL_ID" VARCHAR(16777216))

RETURNS VARCHAR(16777216)

LANGUAGE SQL

EXECUTE AS OWNER

AS 'BEGIN

UPDATE Emp_DB.Emp_Schema.Employee SET Email_ID= :Email_ID WHERE ID = :ID;

END';

Below code snippet can be used to run a stored proc from ADF via script activity :

declare  
query text := '';
begin
EXECUTE IMMEDIATE 'USE SCHEMA METADATA';
 
query := (Call UpdateEmpDetails(@{pipeline().parameters.ParamValue3_ID},'@{pipeline().parameters.ParamValue4_EmailID}'));
end;

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.