Can we use pre-copy script to set session_context when the sink is azure sql table?

[abratv]

Is there a way to set session_context when using copy activity if the sink is azure sql table?

I'm thinking to use pre-copy script, is it using the same connection?

[v-sdhruv]

Hi @abratv ,

If syntax and permissions are all in place and its still not working, I would suggest you to raise it in Issues forum 
Issues - Microsoft Fabric Community
or submit a Support Ticket-
Microsoft Fabric Known Issues

Hope this helps!

[abratv]

Created ticket here

https://community.fabric.microsoft.com/t5/Fabric-Ideas/Run-Copy-Activity-to-Azure-Sql-as-sink-with-s...

[abratv]

that cools, thanks i will test it

[v-sdhruv]

Hi  @abratv ,

Yes, you can use a pre-copy script in Azure Data Factory's Copy Activity to execute SQL commands before the data is written to the sink. This includes setting session-level configurations like Session-Context.The pre-copy script runs on the same connection used for the sink, which means any session-level settings will persist during the data write operation.

Troubleshoot copy activity performance - Azure Data Factory & Azure Synapse | Microsoft Learn

You can specify a pre-copy script like:

EXEC sp_set_session_context @key = N'YourKey', @value = N'YourValue';

Hope this helps!

[abratv]

It suddenly does not work today, seems sp_set_session_context being ignored, are you sure this will work with pre-copy script?

[v-sdhruv]

Hi @abratv ,

If sp_set_session_context  is being ignored ,then it may be due to syntax issue or  permission issue.

The user executing statementmust have the EXECUTE permission on the procedure. If permissions are insufficient, the procedure might silently fail.

You can check the syntax from here-
sp_set_session_context (Transact-SQL) - SQL Server | Microsoft Learn
Additionally, check out this similar post-
Solved: Re: When to Use Pre-Copy Script vs. Source Query i... - Microsoft Fabric Community

Hope this helps!

[abratv]

There is no error when running pre-copy script
100 % no issue with the syntax

It's also bit difficult without clear statement in Microsoft documentation.

In discussion within the link that you gave,
Pre-copy activity runs in a separate session, then copy command runs.