Script Activity not supporting multi-instruction with new Snowflake connector

Question

Script Activity not supporting multi-instruction with new Snowflake connector

DavidR 40

I have a pipeline, in Azure Data Factory that has an "Activity Script" that executes 3 instructions against Snowflake. The script was working fine until I have updated the Snowflake connection in order to remove the legacy Snowflake connection as you recommended. But now, my script does not work. I have seen in the Snowflake logs that the new connection executes this instruction in Snowflake: "alter session set multi_statement_count = 1;"; this means that the Activity Script only supports one instruction. How can I set the multi_statement_count parameter in order to be 0? (with the legacy Snowflake connection I have seen in Snowflake logs that an instruction "alter session set multi_statement_count = 0;" is executed).

Thanks in advance

LeelaRajeshSayana-MSFT 17,766 Reputation points Moderator

2024-03-22T00:05:07.3+00:00

Hi @DavidR Greetings! Welcome to Microsoft Q&A forum. Thank you for posting this question here.

We are looking internally to see if there are any changes in the connector that could have possibly caused this behavior in the script activity.

In the meantime, you may set the first command of the Script activity to alter session set multi_statement_count = 0; and run test the script activity. This should force the connector to look and execute subsequent commands. Please give this a try and let us know if this helps.
DavidR 40 Reputation points

2024-03-22T09:38:07.76+00:00

The command "alter session set multi_statement_count = 0;" is not executed, because snowflake detects that there is more than one command and raise the error: "Actual statement count 4 did not match the desired statement count 1."
Ian Johnson 0 Reputation points

2024-04-09T22:08:13.73+00:00

I can confirm this behavior as well.

Using Legacy Snowflake connector - in Snowflake log first:
"alter session set multi_statement_count = 0;"
is executed.

And then multiple queries execute no problem.

Using New Snowflake connector - in Snowflake log first:
"alter session set multi_statement_count = 1;"
is executed

And then multiple queries FAIL with error from Snowflake:
Actual statement count 4 did not match the desired statement count 1.

I've this many times across multiple Snowflake/ADF instances.

Use ADF Script Activity
Write Query such as the following:
UPDATE TABLE SET A = B;
UPDATE TABLE SET A= B;

Run once with Legacy connector - works no problem.

Run once with New Snowflake connector - impossible to run.
Mathias Luettge 5 Reputation points

2024-06-03T10:09:05.8966667+00:00

Set the multi_statement_count to 0 on user level. Unfortunately, it still throws the error when running script by data factory.

5 answers

Your answer

LeelaRajeshSayana-MSFT 17,766 Reputation points Moderator

2024-03-22T00:05:07.3+00:00

Hi @DavidR Greetings! Welcome to Microsoft Q&A forum. Thank you for posting this question here.

We are looking internally to see if there are any changes in the connector that could have possibly caused this behavior in the script activity.

In the meantime, you may set the first command of the Script activity to alter session set multi_statement_count = 0; and run test the script activity. This should force the connector to look and execute subsequent commands. Please give this a try and let us know if this helps.
DavidR 40 Reputation points

2024-03-22T09:38:07.76+00:00

The command "alter session set multi_statement_count = 0;" is not executed, because snowflake detects that there is more than one command and raise the error: "Actual statement count 4 did not match the desired statement count 1."
Ian Johnson 0 Reputation points

2024-04-09T22:08:13.73+00:00

I can confirm this behavior as well.

Using Legacy Snowflake connector - in Snowflake log first:
"alter session set multi_statement_count = 0;"
is executed.

And then multiple queries execute no problem.

Using New Snowflake connector - in Snowflake log first:
"alter session set multi_statement_count = 1;"
is executed

And then multiple queries FAIL with error from Snowflake:
Actual statement count 4 did not match the desired statement count 1.

I've this many times across multiple Snowflake/ADF instances.

Use ADF Script Activity
Write Query such as the following:
UPDATE TABLE SET A = B;
UPDATE TABLE SET A= B;

Run once with Legacy connector - works no problem.

Run once with New Snowflake connector - impossible to run.
Mathias Luettge 5 Reputation points

2024-06-03T10:09:05.8966667+00:00

Set the multi_statement_count to 0 on user level. Unfortunately, it still throws the error when running script by data factory.

Answer 1

Laan, Niels 10

As a workaround, you can wrap your SQL code in between BEGIN ... END statements.

BEGIN

UPDATE TABLE SET A = B;

UPDATE TABLE SET A= B;

END;

Mathias Luettge 5 Reputation points

2024-06-03T10:04:24.2933333+00:00
The Syntax should be:

BEGIN; ... COMMIT;

This is also not working using script in ADF with LinkedService V2.
Laan, Niels 10 Reputation points

2024-06-03T13:17:56.9533333+00:00

we have scripts like:
BEGIN

TRUNCATE TABLE IF... ;

INSERT INTO (...)

(SELECT ...

FROM ...

WHERE ... );

END;

Which execute nicely with the V2 Connector...
Mathias Luettge 5 Reputation points

2024-06-03T20:46:51.2033333+00:00
Thanks Niels,
did you find a way to use variables (SET xyz = (select something)) as well? We use some scripts that set dynamic identifiers for fields and tables (IDENTIFIER($xyz)).

Already played around with DECLARE.

Edit: Now it works ...

DECLARE res RESULTSET; BEGIN LET myfield VARCHAR DEFAULT (SELECT REGEXP_REPLACE('test_field', '^test_','') || '_uri'); res := (SELECT DISTINCT field1, IDENTIFIER(:myfield) AS field_uri FROM mytable RETURN table(res); END;
Laan, Niels 10 Reputation points

2024-06-04T06:57:34.3966667+00:00

@LuettgeM In regards to not be able to use script parameters anymore with the V2 connector.. We use pipeline parameters instead. And create a dynamic SQL with the pipeline parameter in it.

Script task query begin:

It's not ideal, preferrable Microsoft solves these shortcomings in the Linked Service before the end of October 2024, but at least we have a workaround.

Answer 2

LeelaRajeshSayana-MSFT 17,766 Moderator

Hi @DavidR Apologies for the delayed feedback on this. For a deeper investigation and immediate assistance on this issue, please file a support request @ https://aka.ms/azsupt? If you do not have access to a support plan, could you please send an email to azcommunity@microsoft.com with the below details, so that we can work closely on this matter.

Thread URL: Link to this thread.

Azure Subscription ID:

Email Subject : Attn Leela

Answer 3

Ian Johnson 0

A workaround. In Snowflake, wrap all your SQL statements with a stored procedure. Then in ADF Script Activity call only your Stored Procedure.

Answer 4

Amit Pandit 0

ADF snowflake:

I have same issue with the error message 'Actual statement count 2 did not match the desired statement count 1'

The workaround to stored procs doesnt seem feasible since that will I have some dynamic strings to be executed, and that will be too much extra effort.

DavidR 40 Reputation points

2024-05-30T07:50:51.03+00:00

Neither for me, I have to modify a lot of pipelines that were developed a lot of time ago from differetn teams. The solution must be Microsoft fix the problem they created. It seems Microsft solutions are not very resilient, you can not force an update and at the same time create an incompatibility in this update.
Sivaram Yarru 0 Reputation points

2024-06-07T15:52:39.77+00:00

Hello,

We are still using legacy connector, as it will be a huge impact and change in our code if the multi statement execution and script parameters are not supported for V2 snowflake connector. Did anyone hear back from the Microsoft team on this?

Thanks,

Sivaram

Answer 5

Marco Flores 0

Late in the conversation, but You can use the Script: "non query" option instead "Query" , and you can run multiples statements with out problem.

I hope this helps.

Sandesh Kadam 0 Reputation points

2024-10-09T08:57:20.37+00:00

Hello Marco,
May be its late to check with you, but have you tried using with the stored proc parameters, The parameters block is completely disappeared in the new connector!

wonder how can i pass the parameters, i tried hard, using variables, the call statement itself changes to the Object as @Call... but yes, if i pass the pure value i am able to excute the stored proc.

Share via

Script Activity not supporting multi-instruction with new Snowflake connector

5 answers

Your answer