How to execute SP_SET_SESSION_CONTEXT from dataflow

Question

Hi everyone,

In my database i have RLS setup using session variables. i need to execute "EXEC SP_SET_SESSION_CONTEXT" every time before getting data. based on the session variables values SQL server return the data. How can I execute "EXEC SP_SET_SESSION_CONTEXT" in ADF dataflow. Please anybody help me out on this.

Answer 1

Hi @Arunkumar ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

In order to execute "EXEC SP_SET_SESSION_CONTEXT" every time before getting data, you will need to create a parent store procedure which contains the EXEC SP_SET_SESSION_CONTEXT and your actual query using those variables from the session context as shown below.

Here is an example script for above:

CREATE PROCEDURE sp_RLSSessionExecution @inputValue int
AS
Begin
EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=@inputValue;
SELECT * FROM Sales;
End

Then In data flow source, go to Source options and for input select Stored procedure and in the drop down select the parent stored procedure you have created in the above step and if you have any procedure parameters you can hard code the values or you can use dynamic content option to pass values dynamically from dataflow parameters.

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.