question

AdityaRaj-8613 avatar image
0 Votes"
AdityaRaj-8613 asked ShaikMaheer-MSFT commented

Parse Error In Lookup For Stored Procedure In ADF

I am trying to run a stored procedure from lookup inside adf.

CREATE OR REPLACE PROCEDURE merge_proc(src_table TEXT, tgt_table TEXT, unique_columns TEXT)
RETURNS float
not null
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
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 DEMO_DB."+ tgt_table + " as target using (select "+ schemadetails.join() " from DEMO_DB." + 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;


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 (result_set1.next()) {
schema_arr.push(result_set1.getColumnValue(2));
}
return schema_arr;
}

$$
;
call merge_proc('abc','xyz','cfd');


Getting a parse error: {"error":{"code":"BadRequest","message":"ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression "}"}

azure-data-factory
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

t is throwing an error
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [0A000] Multiple SQL statements in a single API call are not supported; use one API call per statement instead.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [0A000] Multiple SQL statements in a single API call are not supported; use one API call per statement instead.,Source=Snowflake - Latest version supported by Snowflake: 2.21.1,'
Source

0 Votes 0 ·

Hi @AdityaRaj-8613 ,

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

Could you please let us know which type of dataset you are using inside lookup activity? If it SQL server type or Snowflake other any other?

0 Votes 0 ·

Snowflake dataset

0 Votes 0 ·

Hi @AdityaRaj-8613 ,

Following up to check is below provided answer helps you? If yes, Please Accept Answer. Accepting answer helps community too. Please let us know if any further queries. Thank you.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered

Hi @AdityaRaj-8613 ,

Thank you for posting query on Microsoft Q&A and sharing details on requested clarifications.

Unfortunately, at this moment running multiple SQL statements in Lookup Activity to Snowflake is not supported in ADF.

However, there is a feedback item already opened for same. Kindly consider up-voting for this feedback item. Product team will prioritize most up-voted feedback items for future releases. Thank you.

Feed back Item link: https://feedback.azure.com/forums/270578-data-factory/suggestions/42663154-allow-multiple-sql-statements-to-be-run-in-lookup

Please let us know if any further queries. Thank you.


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.