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.

Answer 3

It does appear that Microsoft has deprecated (unintentionally?) the ability to use script parameters in the new Snowflake connector. I had to do a triple take to ensure I wasn't missing anything but upon upgrading all of our connectors in the last couple of days it does appear that previous parameterized Script component calls are no longer working. This would strike me as an oversight and a high profile "bug" versus a new "enhancement" if so. Is there a priority on getting this fixed?

Cheers & Regards,

Jonathan Armstrong