September 20, 2025 at 8:19 pm
Hi everyone
I am writing an SP where there is logic inside the SP that depends how the SP was called. For example,
Start of SP
....code....
If SSIS called SP then do A else do B
.... code ....
End of SP
Is there a way for SP to know who asked it to run? In my case, the choice would be either SSIS ran the SP or I manually ran it.
Thank you
September 20, 2025 at 8:28 pm
It already "knows"... That's what SYSTEM_USER returns. For example...
SELECT SYSTEM_USER AS WindowsAccount, be.*
FROM Bakery.dbo.BakeEvents be;
I know, looks really stupid, but the first column is the user running the query or the account it's being run under.
Maybe you want to dump it into a conditional split sort of thing where if it's "User A", do one thing, and if it's "User B" do something else.
September 21, 2025 at 12:15 am
It already "knows"... That's what SYSTEM_USER returns. For example...
SELECT SYSTEM_USER AS WindowsAccount, be.*
FROM Bakery.dbo.BakeEvents be;I know, looks really stupid, but the first column is the user running the query or the account it's being run under.
Maybe you want to dump it into a conditional split sort of thing where if it's "User A", do one thing, and if it's "User B" do something else.
Thanks for the reply. I just tested this. If I run the SP in SS and then run the same SP in SSIS I get the same information for user. It doesn't differentiate between the two approaches so this is not going to help me because I get the same user info. What do you suggest?
September 21, 2025 at 4:51 am
Are you using your login credentials to run the SSIS packages? I think you need to use different accounts, otherwise, there's no way to tell the two apart.
September 21, 2025 at 11:55 am
SSIS is tricky - specially if executed through the SSIS Catalog (which should be the case... executing from Visual Studio should not be done except during development).
have a look at CONTEXT_INFO - that will allow you to do what you want (e.g. if executing from SSMS you can set it to a a value you then use as criteria)
https://www.sqlservercentral.com/forums/topic/context_info-and-the-sql-calling-stack
September 22, 2025 at 4:03 pm
Maybe explain the use case here: what are you trying to achieve?
There are different ways to capture things.
September 22, 2025 at 11:15 pm
One way to know is to add a parameter that defines the caller. From the place where the procedure is being called, add the necessary parameter.
If you are going to add a parameter, it makes more sense to pass the type of processing needed rather than who called it. The parameter could be @ProcessMode and you pass "A" if you want A and "B" if you want B. The internal code then says,
If @ProcessMode = 'A' then do A else do B.
September 23, 2025 at 3:53 pm
If there are alternate code paths which depend on the client then spare a thought for the optimizer. This pattern implies parameter sniffing imo. To the extent there are separate and distinct pathways consider creating separate procedures. If shared logic exists you could factor it into an inner proc or inline TVF and have both procs call it with (their own) stable parameters
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 1, 2025 at 2:27 am
One way to know is to add a parameter that defines the caller. From the place where the procedure is being called, add the necessary parameter.
If you are going to add a parameter, it makes more sense to pass the type of processing needed rather than who called it. The parameter could be @ProcessMode and you pass "A" if you want A and "B" if you want B. The internal code then says,
If @ProcessMode = 'A' then do A else do B.
I think this is the most simple way to get the logic to work. Thank you for the suggestion!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply