I'm using a join recipe to bring together two datasets. When working through the 'Output dataset' menu, it only allows me to 'Store into' our SQL Servers as a permanent dataset. Is there anyway to output this join to a temp dataset in memory instead?
You can accomplish this by using a SQL pipeline. See SQL pipelines in DSS for an overview of SQL pipelines and how to enable them.
Once SQL pipelines are enabled for your project, you'll want to mark the joined dataset as 'Virtualizable in build' so that it isn't written to disk. You can verify that the pipeline is working by checking the job logs. There should be be a 'SQL pipeline' activity listed that shows which recipes were merged: