Getting Error In Lookup while executing Stored Procedure For Snowflake

Question

CREATE OR REPLACE PROCEDURE schema.merge_proc(SRC_TABLE varchar, tgt_table varchar, unique_columns varchar) RETURNS FLOAT not null LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ function getColumnsToBeInserted(difference) { difference_column = ""; for (i = 0; i < difference.length; i++) { difference_column = difference_column + 'target.'+ difference[i] + " = " +'src.' + difference[i] + "," } return difference_column.slice(0, -1) } function getJoinCondition(UNIQUE_COLUMNS) { var a = UNIQUE_COLUMNS.split(","), i; join_string =""; for (i = 0; i < a.length; i++) { join_string = join_string +'target.'+ a[i] + " = " +'src.'+ a[i] + "AND" } return join_string.slice(0, -3) } function getschema(schemasql) { var schema_arr = []; cmd1 = {sqlText: schemasql}; stmt = snowflake.createStatement(cmd1); var rs = stmt.execute();; while (rs.next()) { schema_arr.push(rs.getColumnValue(2)); } return schema_arr; } var row_count = 0; var src_table_arr = SRC_TABLE.split("."); var schemasql = "select ordinal_position as position,column_name,data_type from information_schema.columns where table_schema ilike '"+src_table_arr[0]+"'and table_name ilike'"+src_table_arr[1]+"' order by ordinal_position" var schemadetails = getschema(schemasql) var unique_columns_arr = UNIQUE_COLUMNS.split(",") var difference = schemadetails.filter(x => !unique_columns_arr.includes(x)); var column_values = (schemadetails.map(i =>'src.'+ i)).join(); sql_command = "merge into "+ TGT_TABLE + " as target using (select "+ schemadetails.join() +" from " + SRC_TABLE + " ) src on " + getJoinCondition(UNIQUE_COLUMNS) + " when matched then update set " + getColumnsToBeInserted(difference) + " when not matched then insert (" + schemadetails.join() + ") values (" + column_values +")" var stmt = snowflake.createStatement( { sqlText: sql_command } ); var res = stmt.execute(); res.next(); row_count = res.getColumnValue(1); return row_count; $$ ;

This is SP ,I am trying to execute using lookup activity. I am getting error:
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid:

Answer 1

Hello @Aditya Raj and welcome to Microsoft Q&A.

If I understand correctly, you are using Lookup Activity to execute a Stored Procedure. The Stored Procedure is running as expected, but Data Factory is throwing an error?

One possibility, is that no data is being returned.

The Lookup activity expects data to be returned. Not all Stored Procedures return data.

If your Stored Procedure does not return data, try appending a statement which does, such as Select 1;

Might that be the case?