Hey,
Can you show a sample SQL query what you are trying to implement via SSMS?
We can leverage the same thing via Lookup activity in ADF.
Anything SQL that can be done via SSMS can be done via Lookup activity in ADF
Though it has some restrictions with amount of data returned