Azure Synapse Pipeline

[JeromeC283]

I have a Store Procedure that is built using Temp Tables and at the end of the process selecting * from the final Temp Table. When I run the code directly in Azure Synapse the code works just fine. When I run the code using a pipeline that is executing the Stored Procedure in a copy data function it provides the below error.

All the temp tables are called #Something. I am seeking help on how to get the code to successfully run.

Operation on target Execute Daily Throughput SP failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name '#FinalOutput'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name '#FinalOutput'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=0,Errors=[{Class=16,Number=208,State=0,Message=Invalid object name '#FinalOutput'.,},],'

[#2]

@JeromeC283 It is likely the pipeline is generating multiple sessions.  To debug try creating FinalTable as a permanent table.  If that works, that is the issue.  You could try creating a permanent table with a GUID (newid() function) appended to the name to work around this issue.